RANKING a measure based on joined table in Direct Query Mode

[BalaKrish]

Dear All,

I am connecting my sql server using direct query mode.

I have two tables. As mentioned below,

Customer Table :

Account ID    Account Name

AA1                 Chris

AA2                 Jack

AA3                 John

Transactions Table:

SNo                Account ID      Deposits Flag          Amount

001                       AA2                 1                           1000

002                       AA2                 1                           2000

003                       AA2                 1                           1000

004                       AA2                 0                           51000000

005                       AA3                 1                           100

006                       AA3                 1                          4000

007                       AA1                 1                           5000

I have to do two things:

Count the no of Deposits Flag which is =1

and also rank it.

The final should be :

Account Id      Account Name       Count_Deposits   Rank_Deposits

1                     Chris                        1                            3

2                     Jack                          3                            1

3                      John                         2                            2

I am able to count using the

Count_Deposits:  Calculate(Count(Count_Deposits),Count_Deposits=1)

Rank_Deposits:    RankX(ALL(Transactions_Table[Account_ID)), Count_Deposits)

The Rank Deposits is working fine If the Account_Name from Customers_Table is not there. It is not working fine when it is pulled inside.

Can you please tell me what should I do to make it right?

Thanks in advance!

Regards,

Bala

[v-alq-msft · Accepted solution]

Hi, @BalaKrish 

Based on research, you may modify the measures as follows.

Count_Deposits = 
CALCULATE(
    COUNT('Transactions Table'[Deposits Flag]),
    FILTER(
        'Transactions Table',
        'Transactions Table'[Deposits Flag] = 1
)
)

Rank_Deposits = 
RANKX(
    ALLSELECTED('Customer Table'),    
    [Count_Deposits]

)

Result:

To hide the row of Rank_Deposits, you may create a measure as below and put it in the visual level filter.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @BalaKrish 

if you are trying to add these measure to Customer table then use this

Rank_Deposits = RankX(ALL('Customer Table'),[Count_Deposits])

do not hesitate to give a kudo to useful posts and mark solutions as solution

[BalaKrish]

This doesnt work when related tables are in the RANKX.

It gives wrong results... 

The requirement is : I need to include AccountID , Count_Deposits From Transactions_Table and Account_Name from Customer_Table