DAX to show top N item name based on row count

[SamiS]

Hi,

I have a fact table where each row is labeled by columns IssueType and IssueSeverity.

I need a measure that prints the most common IssueType (based on rows labeled by it) and another measure that prints row count for that issue.

Also I need similar measures for 2. common and 3. common IssueTypes and row counts.

I managed to do that for a table visual. It tells me top 3 IssueTypes and counts.

However, I want to use separate top three items on podium-like visuals or in Smart narrative visual and then using table is not an option.

I have this measure for ranking (works for table visual):

RankFaults = RANKX(ALL(IssueTypesTable), [CountRows])

CountRows measure used above is:

CountRows = CALCULATE(COUNTROWS(MyFactTable), MyFactTable[IssueType]<>"")

I have tried something like this to pick an N item but my tries doesn't work:

Top2fault = CALCULATE(FIRSTNONBLANK(IssueTypesTable[IssueType], IssueTypesTable[IssueType]), [RankFaults]=2)

How to resolve this?

Thanks!

[v-robertq-msft]

Hi, @SamiS 

Glad to hear that you have solved the problem by creating the measures instead of columns yourself, would you like to mark your own reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, 

According to your description, I can roughly understand your requirement,  I think you can try to create a summary table to create these measures to achieve your requirement like this:

This is the test data I created based on your description:

You can first create a table like this:

Expected table =

SUMMARIZE('MyFactTable',MyFactTable[IssueType],"Count",COUNT(MyFactTable[IssueType]))

Then create two measures in the table like this:

count of 3rd common IssueType =

CALCULATE(MAX('Expected table'[Count]),FILTER(ALL('Expected table'),[Rank]=3))

what the 3rd common IssueType is =

CALCULATE(MAX('Expected table'[IssueType]),'Expected table'[Rank]=3)

And you can create a smart narrative to place the measures to get what you want like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[SamiS · Accepted solution]

Hi, thank you for replying.

I tried your table method, but the problem was that rank values were now fixed. If I use visual slicers to filter the fact table the rank will show me always same value. It is not dynamic.

-------------------------------------------------------------------------

However. I JUST SOLVED THIS (only measures used) and here we go.

-------------------------------------------------------------------------

I needed following measures

1. Count Rows measure:

CountRows = CALCULATE(COUNTROWS(MyFactTable), not(isblank( (MyFactTable[IssueType] )) )

2. Measure to give rank for each Issue type based on 'CountRows' measure:

IssueRank = RANKX(ALL(IssueTypesTable), [03_CountRows])

3. Measure that gives me the IssueType by selected rank:

Rank3IssueType = MAXX(FILTER(ALL(IssueTypesTable[IssueType]), [IssueRank]=3), IssueTypesTable[IssueType])

4. Measure that gives me the count of IssueType by rank:

Rank3IssueTypeCount =

var Rank3 = MAXX(FILTER(ALL(IssueTypesTable[IssueType]), [IssueRank]=3), IssueTypesTable[IssueType])

return CALCULATE(COUNTROWS(MyFactTable), MyFactTable[IssueType]=Rank3)

Now ranks are dynamic because they are recalculated when I use slicers to filter data.

Hopefully this helps someone else.

Thanks to all who offered their help to solve this!

[sonalisaha2310]

Hello, it worked for me, now i have one more doubt
I have a another column called resolution type , i want to rank top resolution type for rank 1 issue type 
for example - i want a dax measure , which basically gives me top 3 resolution type , for issue_type_rank = 1 and also give the count of resolution type. 

Here in issue type , sev1 issue is ranked 1 issue type and resolution type are the top 3 resolution type of ranked 1 issue type , and also find the count of it. It should change dynamically on filters/ slicer choice applied 

[amitchandak]

@SamiS ,

Change this

CountRows = CALCULATE(COUNTROWS(MyFactTable), not(isblank( (MyFactTable[IssueType] )) )

Try TOPN like

CALCULATE([CountRows], TOPN(10,ALLSELECTED(IssueTypesTable[IssueType]),[CountRows],dense), ALLSELECTED(IssueTypesTable[IssueType]))

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

[SamiS]

Thank you for replying. I think I didn't get answered yet.

TOPN prints total row count of top 3 IssueTypes which is 285 in my example. That wasn't my goal.

My goal: Two measures - First shows the count of e.g. 3rd common 'IssueType' (not top 3). Second one tells me what the 3rd common 'IssueType' is.

1. I only want row count of rank 3 IssueType that is 14 in my example. Or rank 2 that is 60.
2. I want to get rank 3 IssueType name as a result that is 'Dirt' in my example.