Create Slicer from Measure

[Anonymous]

Hi, I have a measure that has 5 possible results; H, MH, M, ML, and L.

I need a way of using the results of this measure as a slicer for other visuals in my report

The measure is as follows

Risk Words = if([Risk Score]>=80,"H",if([Risk Score]>=60,"MH",if([Risk Score]>=40,"M",if([Risk Score]>=20,"ML","L"))))

Where 'Risk Score' is

Risk Score = max('Country Risk'[Money Laundering])*(((((if(isblank(max('Controversy DATA'[Anti-Money Laundering])),50,iferror(max('Controversy DATA'[Anti-Money Laundering]),50))+if(isblank(max('GICS Industry Risk Scores'[Anti-Money Laundering])),50,iferror(max('GICS Industry Risk Scores'[Anti-Money Laundering]),50)))/4)-25)/100)+1)

I have tried going down the route of a disconnected table, though I can't figure out how when one value from the table s selected how do I create another measure that filters the other visuals to display only when 'Risk Words' = the selected measure.

Any help would be much appreciated!

[Greg_Deckler]

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[Anonymous]

Thanks so much for your reply Greg, I have been trying to use this post as a guide but to no avail.

I have created a Measure for each of the 5 possibilities as well as the standalone table

e.g. 

H = if([Risk Score]>=80,"H",BLANK())

MH = if(and([Risk Score]>=60,[Risk Score]<80),"MH",BLANK())

and then I am trying to use the linking measure

Disconnected Table Trick = if(HASONEVALUE(RiskNamesAbbr[Risk Levels]),switch(values(RiskNamesAbbr[Risk Levels]),"H",[H],"MH",[MH],"M",[M],"ML",[ML],"L",[L]))

Am I missing something as I still can't get it to work

[karun_r]

Can you share a sample pbix file ? When you use the disconnected table trick as mentioned in Greg's post, it will work only with certain dimensions that you have configured your DAX with. I should be able to help you if I can get some reproducible pbix.

[Anonymous]

Hi Karun, Please find the .pbix file here:

https://1drv.ms/u/s!ApbhmnVrQgL6gjCMwJ1oAMvXx4Vs 

My disconnected table is called RiskNamesAbbr. I am trying to connect it to the output of the measure 'Modern Slavery Risk Words.' The end goal is to be able to slice the report with the cards along the top/on the left updating

Thanks so much for looking into this for me! 

[karun_r]

So, if I understand correctly, you want the Risk Level slicer to work on all applicable metrics that we can see in the "Supply Chain Risk - Modern Slavery" right ?

This might be a bit time consuming as you would have to create proper measures for all the metrics that you intend to slice using Risk Level. I have provided a mock up for the Vendor Count metric. I think it could be further optimized but the basic layout is there to get you started.

File is available here

https://1drv.ms/u/s!ArzOmsqYpUOUlh81hUAHQFdAZDcF?e=U2CuKt

[Syndicate_Admin]

I can't download the file:(

[Anonymous]

Yes, that's correct.

Thankyou, that is exactly what I wanted. I am trying to get your methodology to work for the spend data by substituting 'countrows' for 'sum' however this doesn't seem to work.

the other issue is that I need the cards in the middle to adjust. e.g. if "M" is selected on my slicer, I'd like the highest risk card to also be displaying "M" 

I have been playing with the following measure but to no avail.

MS Risk Words Test = IF(HASONEVALUE(RiskNamesAbbr[Risk Levels]), SWITCH(VALUES(RiskNamesAbbr[Risk Levels]), "H", [Modern Slavery Risk Words], [Modern Slavery Risk Words] = "H", "L",[Modern Slavery Risk Words], [Modern Slavery Risk Words] = "L","M", [Modern Slavery Risk Words], [Modern Slavery Risk Words] = "M","MH", [Modern Slavery Risk Words], [Modern Slavery Risk Words] = "MH","ML", [Modern Slavery Risk Words], [Modern Slavery Risk Words] = "ML"),[Modern Slavery Risk Words])

[Ashish_Mathur]

Hi,

This is not very difficult to do but the multiple tables and measures in your file are causing a lot of confusion.  Can you share a much neater version of this file so that i can help.