Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Slicer Using Measure Based on Criteria

Hi,   Is it possible for a slicer to accommodate a measure based on criteria e.g. the screenshot showed criteria for Negative Variance, Negative Variance with No Sales, etc?   //NT      ...
Need Help
hnguy71's avatar
hnguy71
Super User
2 years ago

Hi Anonymous ,

Assuming that the comments within your measure are derived by a logic, yes.

 

Let's assume you have a comment measure with something similar to this:

 

You'll need to create a disconnected slicer table that holds the same value. For example:

 

Next, you'll create a measure to determine which row you should keep:

 

Finally, add a filter on your table by filtering the measure for the value of 1.


To see it in action:

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Do I need to create a separate table in the power query as the initial step?

    • hnguy71's avatar
      hnguy71
      Super User
      2 years ago

      Hi Anonymous , 
      A separate disconnected table, yes, but it doesn't need to be creaetd in power query if you don't feel like it. You can select "New Table" and use the DATATABLE function to create your table:

       




      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,

         

        My Comment is different from the one you'd shown. I created a new table and when I followed your dax all returned to zero as shown.

         

        Comment_drd =
            SWITCH (
                TRUE (),
                'gbkmut_pns_drd'[Variance] = 0.001, "No Revenue",
                'gbkmut_pns_drd'[Variance] < 0 && gbkmut_pns_drd[abs] = 0, "Negative Variance with No Revenue",
                'gbkmut_pns_drd'[Variance] < 0, "Negative Variance",
                'gbkmut_pns_crd'[abs01] = BLANK() && gbkmut_pns_drd[abs] > 0, "No Cost But With Revenue"
                )