Forum Discussion

BalaKrish's avatar
BalaKrish
Helper I
6 years ago
Solved

RANKING a measure based on joined table in Direct Query Mode

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

Interesting Links
Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      BalaKrish
      Helper I
      6 years ago

      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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.