Grabbing rank1 subcategory for rank1 category

[sonalisaha2310]

Hello, Please help,

I have item info table, which is the master table ,I am just interested in Issue_type and Resolution_Type column from the item info table 
My first problem statment was to rank the issue_type that would change dynamically based on the slicer/filters applied. I used the below logic

1. CountRows1 = CALCULATE(COUNTROWS('Item Info'),' Item Info'[[Issue type]]]<>"")
2. I have created a calculated issue table as

Issue_Table = SUMMARIZE(' Item Info',' Item Info'[[Issue type]]])
3. Measure to give rank to each issue type based on CountRows measure:

TypeRank = RANKX(ALL(Issue_Table),([CountRows1]))
4. Measure that gives me issue type by selected rank:

Rank1 ISSUE = MAXX(FILTER(ALL(Issue_Table[[Issue type]]]),[TypeRank]=1),Issue_Table[[Issue type]]])
5. Measure that gives me count of IssueType by rank:

Rank1Issue_Type Count =

Var Rank1 =

MAXX(FILTER(ALL(Issue_Table[[Issue type]]]),[TypeRank]=1),Issue_Table[[Issue type]]])

return

CALCULATE(COUNTROWS('Item Info'),' tem Info'[[Issue type]]] = Rank1)

The above query solved my problem in getting rank1 issue type but now the problem is i want to get rank 1 resolution type for rank 1 issue type, also i want to get its count - rank1 resolution type count based on rank 1 issue type .Refer to the below table for details

Issue Type	Resolution Type
Sev1	Generic
Sev1	Automated
Sev2	Other
Sev1	Generic
Sev2	Generic
Sev1	Other
Sev2	Generic

If i use the above method it will give me rank1 resolution type but not based on rank1 issue type. Please note all the queries are DAX Calculated measure not column measure, column measure was not changing dynamically based on the slicer or filters applied.I want to use these measure in smart narrative

The result should be as 

Rank1IssueType(DAX Measure)	Rank1ResolutionType(Dax Measure)
Sev1	Generic

and

CountRank1IssueType (DAX Measure)	CountRank1ResolutionType(DAX Measure)
4	2

Thank you in advance

[sonalisaha2310]

Hello I am using the below query to get the Rank2 Resolution Type for IssueType1, it shows the error as The end of input was reached
Rank2ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR Rank1ResolutionType = [Rank1ResolutionType]
VAR FilteredTable = FILTER('Item Info', 'Item Info'[Issue type] = Rank1IssueType)
VAR ResolutionTable = SUMMARIZE(FilteredTable, 'Item Info'[Resolution Type], "Count", COUNT('Item Info'[Resolution Type]))
VAR Top2Resolution = TOPN(2, ResolutionTable, [Count], DESC)
VAR ExcludeTop1 = FILTER(Top2Resolution, 'Item Info'[Resolution Type] <> Rank1ResolutionType)
RETURN IF(HASONEVALUE(ExcludeTop1['Item Info'[Resolution Type]]), VALUES(ExcludeTop1['Item Info'[Resolution Type]]), BLANK()), also additionally I want the count of the rank2 resolution type 

Please help

[sonalisaha2310]

Thank You @technolog , I am getting an error as the end of input was reached for the below query
Rank2ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR Rank1ResolutionType = [Rank1ResolutionType]
VAR FilteredTable = FILTER('Item Info', 'Item Info'[Issue type] = Rank1IssueType)
VAR ResolutionTable = SUMMARIZE(FilteredTable, 'Item Info'[Resolution Type], "Count", COUNT('Item Info'[Resolution Type]))
VAR Top2Resolution = TOPN(2, ResolutionTable, [Count], DESC)
VAR ExcludeTop1 = FILTER(Top2Resolution, 'Item Info'[Resolution Type] <> Rank1ResolutionType)
RETURN IF(HASONEVALUE(ExcludeTop1['Item Info'[Resolution Type]]), VALUES(ExcludeTop1['Item Info'[Resolution Type]]), BLANK())

[sonalisaha2310]

I tried resolving it , but it is not able to detect the fields from the table in the last line, can you please suggest something and getting the error as End of input 

[sonalisaha2310]

Hello @technolog , I have got a small issue to fix, Suppose my data looks like below , for sev1 there is a resolution type blank , 

Issue Type	Resolution Type
Sev1	Generic
Sev 1
Sev1	Automated
Sev2	Other
Sev1	Generic
Sev2	Generic
Sev1	Other
Sev2	Generic

Now i have written a dax query to find resolution type rank 2 of Issue Type rank 1 , the query is given below
Rank2ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR FilteredTable = FILTER('Item Info', 'Item Info'[Issue type] = Rank1IssueType)
VAR ResolutionTable = SUMMARIZE(FilteredTable, 'Item Info'[Resolution Type], "Count", COUNT('Item Info'[Resolution Type]))
VAR TopResolution = TOPN(2, ResolutionTable, [Count], DESC)
RETURN MAXX(TopResolution, 'Item Info'[Resolution Type])
I am not getting the correct result 
rank2 resolution type is either detected as generic or automated but not blank , i tried replacing the blank value with "unknown" string , still it doesnot give the correct result

The actual  result should be resolution type - unknown (as blank is replaced with unknown) and resolution count - (total count of resolution type rank2)

Please help !!

[technolog]

The problem you're facing is due to the way TOPN works. When you use TOPN(2, ResolutionTable, [Count], DESC), it will return the top 2 resolution types based on count. However, when you use MAXX(TopResolution, 'Item Info'[Resolution Type]), it will always return the resolution type with the maximum count among the top 2, which is why you're not getting the second rank.

To get the second rank, you need to exclude the top rank from the result of TOPN and then get the maximum of the remaining. Here's how you can do it:

Rank2ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR Rank1ResolutionType = [Rank1ResolutionType]
VAR FilteredTable = FILTER('Item Info', 'Item Info'[Issue type] = Rank1IssueType)
VAR ResolutionTable = SUMMARIZE(FilteredTable, 'Item Info'[Resolution Type], "Count", COUNT('Item Info'[Resolution Type]))
VAR Top2Resolution = TOPN(2, ResolutionTable, [Count], DESC)
VAR ExcludeTop1 = FILTER(Top2Resolution, 'Item Info'[Resolution Type] <> Rank1ResolutionType)
RETURN IF(HASONEVALUE(ExcludeTop1['Item Info'[Resolution Type]]), VALUES(ExcludeTop1['Item Info'[Resolution Type]]), BLANK())

This will give you the second rank of the resolution type for the top issue type. If there's a tie in the count, it will return one of the tied resolution types.

For the count of the second rank resolution type, you can use:

CountRank2ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR Rank2Resolution = [Rank2ResolutionType]
RETURN CALCULATE(COUNTROWS('Item Info'), 'Item Info'[Issue type] = Rank1IssueType, 'Item Info'[Resolution Type] = Rank2Resolution)

This will give you the count of the second rank resolution type for the top issue type. Remember to handle blanks or "unknown" values appropriately in your data or within the DAX measures to ensure accurate results.

[sonalisaha2310]

Thank You so much

[technolog]

I'm happy to help!

[sonalisaha2310]

Thank You @Mahesh0016 
I am facing the below error

Please help !!

[technolog · Accepted solution]

Hi! 

The error "A table of multiple values was supplied where a single value was expected" typically arises when a DAX function returns a table (or multiple values) when it's expecting a single scalar value.

Let's address the error step by step:

1. Rank1ResolutionType Measure:
The error might be occurring in the `Rank1ResolutionType` measure. The function `VALUES()` returns a table, and if there are multiple resolution types for the top issue type, it will return all of them, causing the error.

To fix this, we need to ensure that we're only getting a single resolution type. We can achieve this by ranking the resolution types for the top issue type and then selecting the top one:

Rank1ResolutionType =
VAR Rank1IssueType = [Rank1 ISSUE]
VAR FilteredTable = FILTER('Item Info', 'Item Info'[Issue type] = Rank1IssueType)
VAR ResolutionTable = SUMMARIZE(FilteredTable, 'Item Info'[Resolution Type], "Count", COUNT('Item Info'[Resolution Type]))
VAR TopResolution = TOPN(1, ResolutionTable, [Count], DESC)
RETURN MAXX(TopResolution, 'Item Info'[Resolution Type])

2. CountRank1ResolutionType Measure:
The measure seems correct, but to ensure that `Rank1ResolutionType` returns a single value, you should use the updated `Rank1ResolutionType` measure from the first step.

I hope that this help you!😎

[technolog]

@sonalisaha2310 

[Mahesh0016]

@sonalisaha2310  I hope this helps you! Thank You!!

1. Calculate Rank 1 Issue Type: You already have a measure to calculate the rank 1 issue type:

DAX

Rank1 ISSUE = MAXX(FILTER(ALL(Issue_Table[[Issue type]]), [TypeRank] = 1), [Issue type])

1. Calculate Rank 1 Resolution Type for Rank 1 Issue Type: To get the rank 1 resolution type for the rank 1 issue type, you can create a new measure that filters based on both the issue type and resolution type. Assuming you have a 'Resolution Type' column in your data:

DAX

Rank1ResolutionType = VAR Rank1IssueType = [Rank1 ISSUE] RETURN CALCULATE( VALUES('Item Info'[Resolution Type]), 'Item Info'[Issue type] = Rank1IssueType, ALL('Item Info'[Resolution Type]) )

1. Calculate Count of Rank 1 Issue Type: You can use your existing measure to calculate the count of the rank 1 issue type:

DAX

CountRank1IssueType = CALCULATE( COUNTROWS('Item Info'), 'Item Info'[Issue type] = [Rank1 ISSUE] )

1. Calculate Count of Rank 1 Resolution Type for Rank 1 Issue Type: For the count of rank 1 resolution type for the rank 1 issue type, you can again utilize the Rank1IssueType calculated variable:

DAX

CountRank1ResolutionType = VAR Rank1IssueType = [Rank1 ISSUE] VAR Rank1ResolutionType = [Rank1ResolutionType] RETURN CALCULATE( COUNTROWS('Item Info'), 'Item Info'[Issue type] = Rank1IssueType, 'Item Info'[Resolution Type] = Rank1ResolutionType )

Now you have four measures:

1. Rank1 ISSUE: Returns the rank 1 issue type.
2. Rank1ResolutionType: Returns the rank 1 resolution type for the rank 1 issue type.
3. CountRank1IssueType: Returns the count of the rank 1 issue type.
4. CountRank1ResolutionType: Returns the count of the rank 1 resolution type for the rank 1 issue type.

Using these measures, you should be able to achieve the desired results. Just remember that these measures depend on the proper setup of your data model and slicer/filter interactions in your Power BI report.