Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
Solved! Go to 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!😎
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
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())
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
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 !!
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.
Thank You so much
I'm happy to help!
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!😎
@sonalisaha2310 I hope this helps you! Thank You!!
Now you have four measures:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |