Dax - trouble with writing a simple RANKX expression

Question

I should start by saying I'm using a Live Connection over a Tabular model, so creating calculated columns with 'edit queries' is not an option unfortunately. Here's what my data looks like, with expected results:

DATA

In my table visual, I need to create a measure that will rank my CASE_IDs, from '1' to 'N', whenever a CASE_ID has more than 1 SUB_CASE_IDs. My end goal: to have a distinct values of CASE_IDs. After the measure is created, the idea is to filter out all the rows that are different than 1.

Some key points if I may:

1. I don't really care for the order of the SUB_CASE_IDs, or which row gets the value of '1'.
2. I know that by removing the SUB_CASE_ID column I get the distinct values of the CASE_IDs, but the thing is, I need to include the SUB_CASE_ID column because it adds another granular level (more rows) that I need to display, which I then also filter using other DAX measures.
3. In SQL, the syntax would look like this: rank() over(partition by case_id order by sub_case_id)

Hope I'm clear enough. Any help would be greatly appreciated!

Answer 1

You can try this solution:

Rank = CALCULATE(
            COUNTROWS('Table1'),
            FILTER(
                ALL(Table1),
                'Table1'[case_id] = EARLIER('Table1'[case_id]) && 
                'Table1'[SomeOfYourValueColumn] > EARLIER('Table1'[SomeOfYourValueColumn])
                )
                )+1

Answer 2

If there are no duplicate sub_case_id for a case_id, this following measure can give your expected output-

row_number = 

CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALLEXCEPT(
            your_table_name,
            your_table_name[case_id]
        ),
        your_table_name[sub_case_id] <= MIN(your_table_name[sub_case_id])
    )
)

Now, if you need to keep selected filters, the Measure will be bit different as below-

row_number = 

CALCULATE(
    COUNTROWS(your_table_name),
    FILTER(
        ALLSELECTED(your_table_name),
        your_table_name[sub_case_id] <= MIN(your_table_name[sub_case_id])
        && your_table_name[case_id] = MIN(your_table_name[case_id])
    )
)

Here is the sample output-