Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
priyamvid
Regular Visitor

Need help in DAX for Table Filter

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

1 ACCEPTED SOLUTION

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.

visual filter.PNG

 

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

13 REPLIES 13
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

Instead of using IF(COUNTROWS) = 1 try using IF(HASONEVALUE) for IndSlicer

I tried using HASONEVALUE also and it is producing the same result.

Can you share your pbix or some sample data?

Sure, I can send.

 

Do you have skype?

Here goes the sample data.

 

Table1
Col1Col2Col3
C1D1E1
C2D2E2
C3D3

E3

 

Slicer
1
2
3

 

Logic for Filtered Table which I need.

 

Filtered Table
If Slicer is 1, then Filter Table1 with Col1 = C1
If Slicer is 2, then Filter Table1 with Col2 = D1
Else Return Full Table1

 

 

Regarding sharing the pbix, can you just attach it here or is it too sensitive? Secondly, I can tell you right away why your dax isn't working. You need to use SWITCH() with your slicer table value and give the arguments for each condition. This way, when the dax expression evaluates which SWITCH() condition is true, it will return the filtered table you are looking for. I will take a look at the file/sample data but hopefully this suggestion will get you there.

Can u please help me with the DAX for Switch as you suggested.

 

Also, I have posted the sample data in the earlier poast and pbix is sensitive that is why I am not able to post here but I have provided the sample data.

 

I have charts created on the filtered table so when I select the Slicer, filtered table rows shud change dynamically i.e why I have used this logic. Any other way of achieving the same is also welcome.

 

Regards,

Priyam

Hi @priyamvid

 

Just reading this thread, so you are saying there is no relation between these tables, correct?

 

 



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.

Yes No relation between slicer and Transformed Data table.

So your end goal is to return a filtered table based on the slicer selection?  Or do you want to perform a calculation/aggregation of the values in the table after filtering is applied?

Seems like he is looking for filtered table.



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.

Why don't you just add a column to your data table with If Then logic to put "Slicer 1" if the value in Col1 = "C1", etc?  

 

Then you just add this calc column as the field in the slicer, and when a user selects a slicer value, the resulting table will be filtered to return only the rows that matched the condition of the logic you programed.

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.

visual filter.PNG

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.