Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Gnanasekar
Helper III
Helper III

RANK based on DISTINCT Count of occurrences

I want to achieve the following SQL query in PowerBI measure using DAX (SUMMARISED table😞

 

SELECT A,B,C, COUNT(D), COUNT(DISTINCT D), RANK() OVER(ORDER BY COUNT(DISTINCT D) DESC)

FROM #TABLE

GROUP BY A,B,C

[A, B, C & D] are columns

Actual requirement

Rank an item based on Distinct occourrences such that maximum distinct count would be ranked 1. 

Also note that, applying any filter should dynamically compute the Rank.

Cannot use direct query or table. I need a DAX measure.

1 ACCEPTED SOLUTION

@Gnanasekar

 

Take a look at my file: https://1drv.ms/u/s!AiiWkkwHZChHjyDG5LGALpkRvYll

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

18 REPLIES 18
mobriladigo
New Member


@Gnanasekar wrote:

I want to achieve the following SQL query in PowerBI FetLife IMVU Canva measure using DAX (SUMMARISED table😞

 

SELECT A,B,C, COUNT(D), COUNT(DISTINCT D), RANK() OVER(ORDER BY COUNT(DISTINCT D) DESC)

FROM #TABLE

GROUP BY A,B,C

[A, B, C & D] are columns

Actual requirement

Rank an item based on Distinct occourrences such that maximum distinct count would be ranked 1. 

Also note that, applying any filter should dynamically compute the Rank.

Cannot use direct query or table. I need a DAX measure.


I ought to achive my normal outcome (Image shared prior) utilizing the accompanying table structure. Where Data1 and Data2 are transported in from SQL DB utilizing SQL inquiry. Lines demonstrate the relationship.

Gnanasekar
Helper III
Helper III

Hi @LivioLanzo,

 

Apologies for confusing you.

I have uploaded a sample file with a similar table model, please find the GDRIVE link here

@v-lili6-msft, you may also try the link for better understanding of my requirement. 

 

My expectation is possible in SQL, not sure why its not in PowerBI or how it needs to be written using DAX. Is there any fundamental issue that I have not noticed?

@Gnanasekar

 

Take a look at my file: https://1drv.ms/u/s!AiiWkkwHZChHjyDG5LGALpkRvYll

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

 

Thank you so much Smiley Happy, I have applied the implementation to my original requirement. It's working beautifully!! 

Would you mind sharing your twitter id?

 

I added a column to fetch EmployeeCode using the LogID. Also, can you please explain, why this wasn't working without a schema model? I am basically a SQL BI guy, and PowerBI's intelligence seems to be confusing to me at times!

 

Thanks & Regards,

-Nitin 

(on behalf of Gnanasekar)

Your model was following a sort of Header/Detail scheme so I normalized the EmpID and the Tower Update Date into the DoorLog table and created a star schema, this made it simpler and you did not need to traverse the the TowerLog table anymore to reach the DoorLog table.

 

Would you mind sharing your twitter id?

I am not too active on Twitter but you can check my blog which is still new but getting there: https://xcelanz.com/   , you will find a link to my LinkedIn if you wanna add me there

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thank you once again @LivioLanzo.

Basically, I have summarised the Log table to form dimensions, thus kind of  normalising it. I understand that it would be better to have tables in normalised - start schema for better functionality, in PowerBI.

 

-Nitin.

v-lili6-msft
Community Support
Community Support

hi, @Gnanasekar

After my test on simple sample data,

you may try to use this measure as below:

Rank = 
RANKX (
   ALLSELECTED(Data),
    CALCULATE ( DISTINCTCOUNT ( Data[D] ),ALLEXCEPT(Data,Data[A],Data[B],Data[C]) ),
    ,
    DESC,
    DENSE
)

Result:

6.png

and after select data from slicer

7.png

here is pbix, please try it.

https://www.dropbox.com/s/pi3yq78miy0unsk/RANK%20based%20on%20DISTINCT%20Count%20of%20occurrences.pb...

 

If it is not your case, please share a simple sample pbix file and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

Best Regards,

Lin

 

 

 

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

@v-lili6-msft This works on importing a table and then summarising it using a DAX and then writing a measure, But I need this to be directly used on the imported table by means of a measure, rather than summarizing the m,aster table.

LivioLanzo
Solution Sage
Solution Sage

@Gnanasekar Please post a sample of your table, along with expected result

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo

 

The following is my expectation (a summarised result of a Master table having columns A, B & C using SUMMARISE DAX expression, while D & Rank are aggregated using expressions), 

1.png

 

 I rebuilt your original table and then used these measures:

 

 

Count =
COUNTROWS ( Data )

 

DistinctCountD =
DISTINCTCOUNT ( Data[D] )

 

Rank =
RANKX (
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[A], Data[B], Data[C] ),
        ALLSELECTED ( Data )
    ),
    CALCULATE ( DISTINCTCOUNT ( Data[D] ) ),
    ,
    DESC,
    DENSE
)

 

 

2018-10-11_14-34-23.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thank you @LivioLanzo,


It is working but, the slicing & dicing is not happening due to summarised table. The following is the structure of my table
    'Master Table' = { [Column-A], [Column-B], [Column-C], [Column-D], [Column-DateKEY] }

If I create a summary table named

    'Data' = SUMMARIZE ( Data1, Data1[A], Data1[B], DimDate[year], DimDate[Quarter] & DimDate[Month] ) ==> I will be loosing the ability to slice & dice. cuz i can't group by [Column-DateKEY].

 

So, I tried the following :

[1] t1 Count = COUNTROWS(Data1)

[2] t2 Distinct =
VAR TABLE1 = SUMMARIZE(Data1, Data1[Column-A],Data1[Column-B], DimDate[Year], DimDate[Month], Data2[Column-D])
RETURN COUNTX(TABLE1, Data2[Column-D])
Basically, I tried to modify the DAX as below: 
[3] t3 RankHelp =
VAR TABLE2 = SUMMARIZE(Data1, Data1[Column-A],Data1[Column-B], DimDate[Year], DimDate[Month], Data2[Column-D])
RETURN
RANKX (
CALCULATETABLE (
TABLE2,
ALLSELECTEDTABLE2==> This was throwing some error 
),
CALCULATE ( COUNTX(TABLE2, Data1[Column-D])),
,
DESC,
DENSE
)

Hope you could understand my objective, i.e. trying to use the table imported via SQL (select * from MasterTable) and directly using it to find RANK without creating another summarised table so that I could retain relationship. 

Hi @LivioLanzo,

 

I should achive my expected result (Image shared earlier) using the following table structure. Where Data1 & Data2 are imported from SQL DB using SQL query. Lines indicate the relationship.

1.png

Hi @LivioLanzo,

As requested plese find the sample data:

 

Note:

  1. Column 2 - is a result of joining Tables Data1 & Data2 on IDs.
  2. DimDate table has datekey column to link datekey of tables Data1 & Data2 on Datekeys (extracted from date columns).

data sample

SELECT * FROM [Master Table]
 DISTINCT COUNT & Rank based on below:dimension 1dimension 2dimension 3
RowIDData1[Column-D] :: Data2[Column-A2] [Column-A] [Column-B] [Column-C]
11 :: VALUE-D1VALUE-A1VALUE-B110/9/18 8:29 PM
21 :: VALUE-D1VALUE-A1VALUE-B310/9/18 8:30 PM
32 :: VALUE-D2VALUE-A1VALUE-B110/9/18 8:16 PM
42 :: VALUE-D2VALUE-A1VALUE-B310/9/18 8:16 PM
53 :: VALUE-D3VALUE-A1VALUE-B110/9/18 8:05 PM
63 :: VALUE-D3VALUE-A1VALUE-B310/9/18 8:06 PM
74 :: VALUE-D4VALUE-A1VALUE-B110/9/18 7:50 PM
84 :: VALUE-D4VALUE-A1VALUE-B1710/9/18 7:50 PM
95 :: VALUE-D5VALUE-A1VALUE-B110/9/18 7:28 PM
106 :: VALUE-D6VALUE-A1VALUE-B110/9/18 2:56 PM
1136 :: VALUE-D36VALUE-A2VALUE-B1610/8/18 5:52 PM
1236 :: VALUE-D36VALUE-A2VALUE-B710/8/18 6:16 PM
1335 :: VALUE-D35VALUE-A2VALUE-B710/8/18 6:16 PM
1435 :: VALUE-D35VALUE-A2VALUE-B1610/8/18 5:51 PM
1534 :: VALUE-D34VALUE-A1VALUE-B410/8/18 1:28 PM
1634 :: VALUE-D34VALUE-A2VALUE-B710/8/18 6:17 PM
1733 :: VALUE-D33VALUE-A1VALUE-B410/8/18 12:30 PM
1833 :: VALUE-D33VALUE-A2VALUE-B1610/8/18 5:52 PM
1932 :: VALUE-D32VALUE-A2VALUE-B1610/8/18 5:52 PM
2032 :: VALUE-D32VALUE-A1VALUE-B110/8/18 12:26 PM
2132 :: VALUE-D32VALUE-A2VALUE-B510/8/18 12:26 PM
2231 :: VALUE-D31VALUE-A1VALUE-B110/8/18 12:25 PM
2330 :: VALUE-D30VALUE-A1VALUE-B110/8/18 12:23 PM
2429 :: VALUE-D29VALUE-A1VALUE-B110/8/18 12:23 PM
2528 :: VALUE-D28VALUE-A1VALUE-B110/8/18 12:22 PM
2627 :: VALUE-D27VALUE-A1VALUE-B110/8/18 12:21 PM
2726 :: VALUE-D26VALUE-A1VALUE-B110/8/18 12:10 PM
2825 :: VALUE-D25VALUE-A1VALUE-B110/7/18 5:08 PM
2925 :: VALUE-D25VALUE-A1VALUE-B1510/7/18 5:08 PM
3025 :: VALUE-D25VALUE-A2VALUE-B510/7/18 5:09 PM
3124 :: VALUE-D24VALUE-A1VALUE-B110/7/18 4:06 PM
3224 :: VALUE-D24VALUE-A2VALUE-B510/7/18 4:06 PM
3324 :: VALUE-D24VALUE-A1VALUE-B1510/7/18 4:11 PM
3423 :: VALUE-D23VALUE-A1VALUE-B110/7/18 3:56 PM
3523 :: VALUE-D23VALUE-A1VALUE-B1510/7/18 3:57 PM
3623 :: VALUE-D23VALUE-A2VALUE-B510/7/18 3:59 PM
3722 :: VALUE-D22VALUE-A1VALUE-B110/7/18 1:52 PM
3822 :: VALUE-D22VALUE-A1VALUE-B1510/7/18 1:54 PM
3922 :: VALUE-D22VALUE-A2VALUE-B510/7/18 1:54 PM
4020 :: VALUE-D20VALUE-A1VALUE-B110/7/18 11:45 AM
4120 :: VALUE-D20VALUE-A2VALUE-B510/7/18 11:45 AM
4220 :: VALUE-D20VALUE-A2VALUE-B1610/7/18 12:20 PM
4321 :: VALUE-D21VALUE-A1VALUE-B110/7/18 11:20 AM
4419 :: VALUE-D19VALUE-A1VALUE-B110/6/18 6:10 PM
4519 :: VALUE-D19VALUE-A2VALUE-B510/6/18 6:11 PM
4618 :: VALUE-D18VALUE-A1VALUE-B110/6/18 5:34 PM
4718 :: VALUE-D18VALUE-A1VALUE-B1510/6/18 5:34 PM
4818 :: VALUE-D18VALUE-A2VALUE-B510/6/18 5:34 PM
4916 :: VALUE-D16VALUE-A1VALUE-B110/6/18 5:28 PM
5016 :: VALUE-D16VALUE-A1VALUE-B1510/6/18 5:28 PM
5116 :: VALUE-D16VALUE-A2VALUE-B510/6/18 5:28 PM
527 :: VALUE-D7VALUE-A1VALUE-B110/6/18 5:22 PM
537 :: VALUE-D7VALUE-A2VALUE-B510/6/18 5:22 PM
548 :: VALUE-D8VALUE-A1VALUE-B110/6/18 4:18 PM
558 :: VALUE-D8VALUE-A3VALUE-B810/6/18 4:20 PM
568 :: VALUE-D8VALUE-A3VALUE-B910/6/18 4:21 PM
578 :: VALUE-D8VALUE-A3VALUE-B1010/6/18 4:21 PM
588 :: VALUE-D8VALUE-A3VALUE-B1110/6/18 4:21 PM
598 :: VALUE-D8VALUE-A3VALUE-B1210/6/18 4:21 PM
608 :: VALUE-D8VALUE-A3VALUE-B1310/6/18 4:22 PM
618 :: VALUE-D8VALUE-A3VALUE-B1410/6/18 4:24 PM
628 :: VALUE-D8VALUE-A1VALUE-B510/6/18 4:27 PM
638 :: VALUE-D8VALUE-A1VALUE-B1510/6/18 4:33 PM
648 :: VALUE-D8VALUE-A1VALUE-B410/6/18 4:33 PM
658 :: VALUE-D8VALUE-A1VALUE-B410/6/18 4:34 PM
668 :: VALUE-D8VALUE-A2VALUE-B510/6/18 4:46 PM
679 :: VALUE-D9VALUE-A2VALUE-B210/6/18 2:10 PM
689 :: VALUE-D9VALUE-A2VALUE-B710/6/18 2:11 PM
699 :: VALUE-D9VALUE-A2VALUE-B610/6/18 2:12 PM
7010 :: VALUE-D10VALUE-A2VALUE-B510/8/18 1:01 AM
7110 :: VALUE-D10VALUE-A1VALUE-B410/7/18 11:17 PM
7217 :: VALUE-D17VALUE-A1VALUE-B410/7/18 11:20 PM
735 :: VALUE-D5VALUE-A1VALUE-B410/7/18 11:20 PM

Hi again @Gnanasekar

 

So, I have built the below model with the below data:

 

 

2018-10-12_9-39-16.jpg

 

 

2018-10-12_9-37-42.jpg

 

Then I have added the below measure from which I have got the below results:

 

Notice that I have switched off the totals from the visuals, otherwise I had to add some additional conditions to the measure as in this case having totals would make no sense.

 

You may also want to change the last argument of RANKX from Skip to Dense, in my formula it is set to the default, which is SKIP.

 

 

RankDistinct =
IF (
    ISEMPTY ( MasterTable ),
    BLANK (),
    RANKX (
        CALCULATETABLE (
            SUMMARIZE ( MasterTable, ValuesDimensionA[ColumnA], ValuesDimensionB[ColumnB] ),
            ALLSELECTED ( ValuesDimensionA ),
            ALLSELECTED ( ValuesDimensionB )
        ),
        CALCULATE ( DISTINCTCOUNT ( MasterTable[ColumnD] ) )
    )
)

 

 

 

2018-10-12_9-41-38.jpg

 

 

 Is it what you were after ?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

Can you please try the same using such that you dont group column by Date. Please refer to my expectation image shared earlier.

 

1.png

@Gnanasekar you can apply filters to year, month, date and it should still work as far as I can see

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @Gnanasekar

 

would it be possible to share some sample data? also I am assuming you want to slice it by date?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.