Forum Discussion

priyamvid's avatar
priyamvid
Regular Visitor
9 years ago
Solved

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 diffe...
Need Help
  • parry2k's avatar
    parry2k
    9 years ago

    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

     

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
Can you share your pbix or some sample data?
priyamvid's avatar
priyamvid
Regular Visitor
9 years ago

Sure, I can send.

 

Do you have skype?

  • priyamvid's avatar
    priyamvid
    Regular Visitor
    9 years ago

    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

     

     

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago
      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's avatar
        priyamvid
        Regular Visitor
        9 years ago

        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