Solved: Need help in DAX for Table Filter - Page 2

priyamvid · ‎02-19-2017

I have a table in power bi and I am trying to filter it using some column value.

I want to make the filter as dynamic as possible which means that I need a dax which filters the table using different columns based on the value of slicer (disconnected).

Please help me .

I have already written a DAX for FIltered Table.

FilteredTransformedData =
Var IndSlicer = IF(COUNTROWS(VALUES(Table5[Slicer]))=1, VALUES(Table5[Slicer]), TRUE())
RETURN FILTER(All('Transformed Data'), IF(Exact(IndSlicer,"Top10"),'Transformed Data'[group/Top10],IF(IndSlicer<>"",'Transformed Data'[group/Bottom10],true)))

Here , IndSlicer is always giving blank rows despite I am selecting the Slicer value.

I am not sure what is wrong here. Could anyone help me in this .

Regards,

Priyam

parry2k · ‎02-19-2017

What @dkay84_PowerBI suggested make sense but if you want to stick with your requirement, here is the solution which I think will work:

In your slicer table, add a calculated column to find out which column we want to filter, in case of Option 3 selected in filter, we are not filtering on any column

Which Column to Filter = 
 if(Table2[Slicer]=1, "Column1", if(Table2[Slicer]=2, "Column2", ""))

Now add Measure in your slicer table, to check what value is selected on the slicer and then get the column value, in case option 3 or no value is selected then get Blank value otherwise get column name from previous calculated column we added to the slicer table

Which Slicer Option is Selected = IF(HASONEVALUE(Table2[Slicer]) = FALSE || VALUES(Table2[Slicer])=3, BLANK(), VALUES(Table2[Which Column to Filter]))

Now, go to your data table and add following measure, this is where you will put the logic (if statement):

What values to Filter = if([Which Slicer Option is Selected]=BLANK(),1, if([Which Slicer Option is Selected] = "Column1" && MAX(Table1[Column1])="C1", 1, if([Which Slicer Option is Selected]="Column2" && MAX(Table1[Column2])="D1",1, 2)))

Add this newly calculated measure from your data table to Visual Level Filters and under advance filter, select value is 1, as listed below.

Drop a table with your columns in it from data table, now when you select the value in the slicer, your table will filter based on selection.

I hope it will do the job, if you need pbix file, send me a private message with your email and I will send it over to you.

Cheers,

Parv

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Need help in DAX for Table Filter

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly