DAX CALCULATETABLE With Conditional Filtering Based on SELECTEDVALUE In Slicer

[volod1701]

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?

[bhanu_gautam · Accepted 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.

[v-dineshya · Accepted solution]

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] )

RETURN

    SWITCH(

        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

[v-dineshya]

Hi @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

[v-dineshya]

Hi @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

[v-dineshya]

Hi @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

[volod1701]

@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.

[bhanu_gautam · Accepted 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.