cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BalaKrish
Helper I
Helper I

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

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

d1.png

 

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

d2.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

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:

d1.png

 

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

d2.png

 

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
Super User
Super User

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


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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors