Forum Discussion
DAX CALCULATETABLE With Conditional Filtering Based on SELECTEDVALUE In Slicer
Hello!
Consider I have a single select slicer with some types
| Type_Id | Type_Name |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
And I have some "fact table" which has the column Type_Id
| Id | Name | Type_Id |
| 1 | Row 1 | 1 |
| 2 | Row 2 | 2 |
| 3 | Row 3 | 3 |
| 4 | Row 4 | 4 |
| 5 | Row 5 | 3 |
| 6 | Row 6 | 4 |
So when I select in slicer 1 - Type A, I want to see rows with type 1, 3, 4
When I select in slicer any other type - i want to see rows only with that type
I have tried to generate a solution with AI: CALCULATETABLE with nested SWITCH or IF or TREATAS but it doesn't work.
Can anybody suggest a solution?
volod1701 Create a new measure in your fact table:
DAX
SelectedTypeRows =
VAR SelectedType = SELECTEDVALUE('Type'[Type_Id])
RETURN
IF(
SelectedType = 1,
CALCULATETABLE(
'FactTable',
'FactTable'[Type_Id] IN {1, 3, 4}
),
CALCULATETABLE(
'FactTable',
'FactTable'[Type_Id] = SelectedType
)
)Use this measure in a table visual to display the filtered rows.
This measure checks if the selected type is 1. If it is, it returns rows with Type_Id 1, 3, and 4. Otherwise, it returns rows with the selected Type_Id. This should give you the desired behavior in your report.Hi volod1701 ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps.
1. Create below calculated measure
ShowRow =VAR SelectedType = SELECTEDVALUE ( 'Type'[Type_Id] )VAR CurrentType = MAX ( 'Fact'[Type_Id] )RETURNSWITCH(TRUE(),ISBLANK(SelectedType), 1,SelectedType = 1 && CurrentType IN {1, 3, 4}, 1,SelectedType <> 1 && CurrentType = SelectedType, 1,0)
2. Place this ShowRow measure on a table visual with Fact[Name], Fact[Type_Id]. Use the Visual Filter pane to filter ShowRow = 1.If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
6 Replies
volod1701 Create a new measure in your fact table:
DAX
SelectedTypeRows =
VAR SelectedType = SELECTEDVALUE('Type'[Type_Id])
RETURN
IF(
SelectedType = 1,
CALCULATETABLE(
'FactTable',
'FactTable'[Type_Id] IN {1, 3, 4}
),
CALCULATETABLE(
'FactTable',
'FactTable'[Type_Id] = SelectedType
)
)Use this measure in a table visual to display the filtered rows.
This measure checks if the selected type is 1. If it is, it returns rows with Type_Id 1, 3, and 4. Otherwise, it returns rows with the selected Type_Id. This should give you the desired behavior in your report.bhanu_gautam
It returns this error: "the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". As I understand, if the table must be returned, then RETURN must be followed by something that returns table explicitly. When DAX meets RETURN IF - it expects a scalar measure, not a table, it doesn't looks into the IF function implicitly, so it doesn't understand that IF returns a table in its result so this way it doesn't translate our measure to a table one, but translates to a scalar one.Hi volod1701 ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps.
1. Create below calculated measure
ShowRow =VAR SelectedType = SELECTEDVALUE ( 'Type'[Type_Id] )VAR CurrentType = MAX ( 'Fact'[Type_Id] )RETURNSWITCH(TRUE(),ISBLANK(SelectedType), 1,SelectedType = 1 && CurrentType IN {1, 3, 4}, 1,SelectedType <> 1 && CurrentType = SelectedType, 1,0)
2. Place this ShowRow measure on a table visual with Fact[Name], Fact[Type_Id]. Use the Visual Filter pane to filter ShowRow = 1.If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi volod1701 ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi @volod1701 ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
