Need help in DAX for Table Filter

[priyamvid]

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

[dkay84_PowerBI]

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

[priyamvid]

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

[dkay84_PowerBI]

Can you share your pbix or some sample data?

[priyamvid]

Sure, I can send.

Do you have skype?

[priyamvid]

Here goes the sample data.

Table1
Col1	Col2	Col3
C1	D1	E1
C2	D2	E2
C3	D3	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

[dkay84_PowerBI]

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.

[priyamvid]

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

[parry2k]

Hi @priyamvid

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

[priyamvid]

Yes No relation between slicer and Transformed Data table.

[dkay84_PowerBI]

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?

[parry2k]

Seems like he is looking for filtered table.

[dkay84_PowerBI]

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.

[parry2k · 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.

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