Use the Measure as a slicer in the report view

[cruzp]

Hello PBI Experts,

I have a table in the report view below which I have also extracted as excel (click here: data.xlsx) for you to try.

My expected output is that i would be able to use the DAX measure as a Slicer on top of the table that will give me option wether to select "✔" or just blank. Example below:

The Measures are: 

Project Removed = IF(NOT(ISBLANK([Category Previous])) && [Category Current] = "Null" && 
               ([Category Previous] <> BLANK() || [Category Current] <> BLANK()), 
		"✔", BLANK())

New to R4 = 
VAR AmountPreviousWeek = 
    CALCULATE(
        SUM(REP_REVENUE_R1_R4_SNAPSHOT[AMOUNT]),
        FILTER(
            REP_REVENUE_R1_R4_SNAPSHOT,
            REP_REVENUE_R1_R4_SNAPSHOT[SNAPSHOT_DATE_BK] = MAX('Previous Reporting Date'[Previous Reporting Date])
        )
)
RETURN IF(ISBLANK(AmountPreviousWeek) && [Category Previous] = "Null", "✔", BLANK())

Start Change = IF([Start Previous] <> [Start Current] || ISBLANK([Start Previous]) && 
    ([Category Previous] <> BLANK() && [Category Current] <> BLANK()),
	"✔",BLANK())

End Change = IF([End Previous] <> [End Current] || ISBLANK([End Previous]) && 
	([Category Previous] <> BLANK() && [Category Current] <> BLANK()),
	"✔",BLANK())

Thank you!!

[cruzp]

any help please?

[v-yueyunzh-msft · Accepted solution]

Hi , @cruzp 

According to your descripition, do you mean you want to create the slicers to control the current rows if show based on the measures value?
This is my understand for your need.

Like this , if we select the [Measure 1] = "✔" and [Measure 2] = " " and [Measure 3] =  "✔" .Then we will show just top3 rows in your visual.

If this , you can create tables as the slicer like this:

And then we can create a measure:

Measure = var _select_measure1 =IF( MAX('Table1'[Measure 1])  ="✔" , "✔" , BLANK())

 var _select_measure2 =IF( MAX('Table2'[Measure 2])  ="✔" , "✔" , BLANK())

var _select_measure3 =IF( MAX('Table3'[Measure 3])  ="✔" , "✔" , BLANK())

return

IF([Measure 1]=_select_measure1 && _select_measure2 = [Measure 2] &&  _select_measure3=[Measure 3] ,1 , 0)

Then we can put the measure on the  "Filter on this visual", The result is as follows:

If i misunderstand your need , you can give me an example for your need and your desire result in your need so that we can help you better!~

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cruzp]

Hi @v-yueyunzh-msft , i am having trouble when other measure is added in the visual, the output is different other values are blank.

the "Exception Filter" is the measure to utilize measure as a slicer.

Exception Slicer = var _select_measure1 =IF( MAX('Table1'[Project Removed])  ="✔" , "✔" , BLANK())
        var _select_measure2 =IF( MAX('Table2'[New to R4])  ="✔" , "✔" , BLANK())
        var _select_measure3 =IF( MAX('Table3'[Start Change])  ="✔" , "✔" , BLANK())
        var _select_measure4 =IF( MAX('Table4'[End Change])  ="✔" , "✔" , BLANK())
        var _select_measure5 =IF( MAX('Table5'[Value Change])  ="✔" , "✔" , BLANK())
        var _select_measure6 =IF( MAX('Table6'[Forecast Hours Missing])  ="✔" , "✔" , BLANK())
        
return
    IF(
    _select_measure1 = [Project Removed] && 
    _select_measure2 = [New to R4] &&  
    _select_measure3 = [Start Change] && 
    _select_measure4 = [End Change] && 
    _select_measure5 = [Value Change] && 
    _select_measure6 = [Forecast Hours Missing]

    ,1 , 0)

On the first picture below, this has the correct values for Previous and Current Q1/Q2/Q3/Q4. 

On this second picture, It worked since the values for each slicer follows on what is selected when i selected "Exception Filter" to 1 to utilize the slicer, except for Current Quarter that shows incorrect numbers and Previous Quarter showed Blanks 

do you know how to fix it to make the whole table work?

[v-yueyunzh-msft]

Hi , @cruzp 

Thanks for your quick response! According to your description, when you add another measures and configure the filter measure. The another measures are incorrect and shows blank ....

This is difficult to see why in the screenshot , If it is convenient, can you provide me with your problematic pbix file (do not contain sensitive data), so that I can test it locally.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cruzp]

hi @v-yueyunzh-msft i can provide it but i am not sure how do i create the same problematic pbix file since it has the data model on it with connections to Databases. do you know how can i share without exposing the data/model?

thanks

[v-yueyunzh-msft]

Hi，  @cruzp 

Thank you for your reply, do you mean that some of your tables are connected in DQ mode?

If it is convenient, you can create some test data to simulate the occurrence of the problem, use Excel or directly "Enter Data" to import the data, and then create a visual to reproduce the problem and send it to me.

You only need to import the tables you need to use, other tables can be deleted. And you can create some test data, don't use your production data.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cruzp]

Thank you @v-yueyunzh-msft I appreciate the time and effort of yours.

[cruzp]

Hi @v-yueyunzh-msft i liked the idea, it looks like it would work on my end, but i have to try it first.

question, how do i create a table like below?

[v-yueyunzh-msft]

Hi , @cruzp 

As i see the @bot_damo  have answered your question, and you can create this table in your side . And the 'Table1' , 'Table2',’Table3‘ are all the calculated table i created .

And the MAX('Table1'[Measure 1])  is used to get the slicer selected in the slicer:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bot_damo]

You can use the "Enter Data" function in power bi

[bot_damo]

Hi @cruzp ,

Just from a quick glance, I think you need to put the measures as a calculated column in the table. Then you can use the columns as a slicer.

There may be some nuance to your solution I'm missing. Please let me know if this is feasible.

Kind Regards

[cruzp]

hi @bot_damo how do i do that?

[bot_damo]

Copy the measures into a calculated column inside the table that you are classifying. You'll need to remove the MAX() aggregators.

Kind Regards,

botdamo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[cruzp]

Thank you @bot_damo I appreciate the time and effort of yours.