Slicer Using Measure Based on Criteria

[Anonymous]

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

[Ashish_Mathur]

Hi,

Which are the other crtieria you have?

[hnguy71]

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]

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

[hnguy71]

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]

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"

        )

[hnguy71]

Hi @Anonymous 

Seems you're still missing the slicer on the page. Can you also share what's inside your "Filter Flag" meaesure?

[Anonymous]

Hi,

I created the filter as shown.

I'm also sharing you the dax i created for the filter flag

Filter Flag =

VAR CurrentClassification = gbkmut_pns_drd[Comment_drd]

VAR CurrentSelection = SELECTEDVALUE('Filter Comment'[Selection])

RETURN

SWITCH(

    TRUE(),

    CurrentSelection = "Negative Variance" && CurrentClassification = "Negative Variance",1,

    CurrentSelection = "Negative Variance with No Revenue" && CurrentClassification = "Negative Variance with No Revenue",1,

    CurrentSelection = "No Cost But With Revenue" && CurrentClassification = "No Cost But With Revenue",1,

    CurrentSelection = "No Revenue" && CurrentClassification = "No Revenue",1,

    CurrentSelection = "No Cost, No Revenue" && CurrentClassification = "No Cost,No Revenue",1,

    0

)

[hnguy71]

Hi @Anonymous ,

One thing to keep in mind is that your string inside your comment measure must match the string inside your flag measure:

Filter Flag =
VAR CurrentClassification = [Comment_drd]
VAR CurrentSelection = SELECTEDVALUE('Filter Comment'[Selection])
RETURN
IF(CurrentSelection = CurrentClassification, 1, 0)

 
Also, make sure that there's a selection from your dropdown for it to turn from 0 to 1.

I've linked a sample for you so you can get a feel of what to do:
tksnota_filter_measure.pbix