topic Re: DAX Filter in DAX Commands and Tips

DAX Filter

Anonymous — Wed, 10 Nov 2021 20:17:00 GMT

Hello,

I have created a report that is missing filter column to use in the slicer:

Filter column looks as below:

Item	Result
A	A and AB
B	B and AB
C	C
AB	AB

There is a filter column "Item" that should be used in the slicer. Logic is: if A selected then slicer should include A and AB, if B selected then (B and AB), for C = C and for AB =AB.

I tried to use IF and SWITCH functions but only achived either A with AB or B with AB.

Please help.

Re: DAX Filter

Watsky — Wed, 10 Nov 2021 23:37:49 GMT

Hi Anonymous

How about

IF(CONTAINSSTRING('Table'[Result],'Table'[Item])= TRUE,'Table'[Item],"")

Re: DAX Filter

Anonymous — Thu, 11 Nov 2021 00:09:29 GMT

Hi Watsky thanks for helping.

Sorry if I get it wrong but there is no column called "Result" in my table, I just illustrated as an example in my question. There is only one column "Item" to filter and I want to have custom column as "Result".

Re: DAX Filter

VahidDM — Thu, 11 Nov 2021 00:51:50 GMT

Hi Anonymous

Your request is a little bit tricky😎 .

Please download this file and I hope this cover all your requirements: https://gofile.io/d/Ta9o4y

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: DAX Filter

Anonymous — Thu, 11 Nov 2021 14:16:23 GMT

Thanks VahidDM but for some reason I am not able to download the file.

Re: DAX Filter

v-robertq-msft — Fri, 12 Nov 2021 09:47:43 GMT

Hi,

According to your description, I can clearly understand your requirement, I think you can achieve this using a calculated table for the slicer and a measure as the flag for the visual filter, you can try my steps:

Create a table like this:

Slicer = 'Table'

Create a measure like this:

Flag = var _selected=SELECTEDVALUE(Slicer[Item]) return SWITCH( _selected, "A",IF(MAX('Table'[Item]) in {"A","AB"},1,0), "B",if(MAX('Table'[Item]) in {"B","AB"},1,0), "C",IF(MAX('Table'[Item])="C",1,0), "AB",IF(MAX('Table'[Item])="AB",1,0) )

Then you can create a slicer and a table chart to place the visual filter like this:

And you can get what you want.

You can download my test pbix file below

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.

Re: DAX Filter

Anonymous — Fri, 12 Nov 2021 13:48:58 GMT

That was perfect, thank you for your help v-robertq-msft.