Link measure to disconnected table

[paul10]

Hello

I have a disconnected tables with the values below:

Slicer Table
  Slicer Values
    Amber Turned to Red
    Green Turned to Amber
    Green Turned to Red
    Other
    Red Turned to Amber
    Red Turned to Green
    Remained Amber
    Remained Red

In another table, i have a measure that calculates some statuses. This is working correctly:

Comparison=
SWITCH(true(),
[Latest Value]="R - Red" && [Before Latest Value]="R - Red","Remained Red",
[Latest Value]="A - Amber" && [Before Latest Value]="A - Amber","Remained Amber",
[Before Latest Value]="G - Green" && [Latest Value]="A - Amber", "Green Turned to Amber",
[Before Latest Value]="G - Green" && [Latest Value]="R - Red", "Green Turned to Red",
[Before Latest Value]="A - Amber" && [Latest Value]="R - Red", "Amber Turned to Red",
[Before Latest Value]="R - Red" && [Latest Value]="A - Amber", "Red Turned to Amber",
[Before Latest Value]="R - Red" && [Latest Value]="G - Green", "Red Turned to Green",
"Other")

I am trying to use the slicer table as a slicer in the page and filter the other table based on the possible values returned by the measure above.

I cannot make it work. Currently i am stuck in finding what code to add here:

Comparison Filter =
VAR SelectedFilter =SELECTEDVALUE('Slicer Table'[Slicer Values],"All")
RETURN
SWITCH(true(),
SelectedFilter="Remained Amber", filter based on measure Comparison="Remained Amber",
SelectedFilter="Remained Red",filter based on measure Comparison="Remained Red",
SelectedFilter="Other", filter based on measure Comparison="Other",

etc.

)

Could you please provide some ideas?
Also, is it possible to make this work with multiple selections in the slicer?
I’ve tried creating a column instead of the measure by using the same formula above but all the values are wrong when used in a table. Any idea why?

Thank you

[paul10]

Thank you all for your support and patience. I used @PaulDBrown solution and it worked for my data. Have a great day

[PaulDBrown · Accepted solution]

@paul10 

Here is one way.

The following is a simple dataset. A measure is used to return a colour based on the value of the sum of actuals. I have created a disconected table listing the colours to be used as a slicer. The model looks like this

To show the measure returning colours:

Colour coding = 
var CODE = SWITCH(TRUE(),
    [Sum of Actuals] <4000, "Red",
     [Sum of Actuals] <4500, "Orange",
     [Sum of Actuals] <5000, "Blue",
     [Sum of Actuals] <5500, "Green",
     "Other")
     RETURN
     IF(ISBLANK([Sum of Actuals]), BLANK(), CODE)

 To use the disconnected table as a slicer to filter the rows based on the selected colour, use this measure in the Filter pane for the visual:

FILTER by Colour = 
VAR _colours = VALUES('Colour Slicer'[Colour Code])
VAR code = SUMMARIZE('Calendar Table', "res", [Colour coding])
return
COUNTROWS(INTERSECT(_colours, code))

Which gets you this:

[V-pazhen-msft]

@paul10 
I don't think you could make it to multiple selected slicer. My way is to create new measures in the Slicer table based on the original measures in the other table.

Please check my sample pbix attached.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paul10]

@V-pazhen-msft thank you for your suggestion. it does not work in my case. I have a list of projects in a table and 2 snapshots with historical values for each. I am comparing these 2 historical values for each project to get a status (if it was a change, what type).  I cannot use separate measures to achieve this as in your case, where you are using a chart. I tried it and it will simply display a blank in the measures where the filter does not apply, it does not filter the table. 

Have a great day

[lkalawski]

@paul10 

Could you please share sample of data? It will be easier to suggest some solution for your problem.

_______________
If I helped, please accept the solution and give kudos! 😀 

[amitchandak]

@paul10 , Try like

measure =
Comparison Filter =
VAR SelectedFilter =SELECTEDVALUE('Slicer Table'[Slicer Values],"All")
var _filter =SWITCH(true(),
SelectedFilter="Remained Amber", filter(Table,Table[Comparison]="Remained Amber"),
SelectedFilter="Remained Red", filter(Table,Table[Comparison]="Remained Red") ,
SelectedFilter="Other", filter based on measure Comparison="Other",
filter(Table,Table[Comparison]=Table[Comparison]) //no filter

)
RETURN
calculate([measure],_filter)

[paul10]

@amitchandak  Thank you for your reply. Unfortunately i get an error with this solution:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Greg_Deckler]

@paul10 - OK, this would be far easier with sample data but for the first part of the question, maybe something like:

Comparison Filter =
VAR SelectedFilter =SELECTEDVALUE('Slicer Table'[Slicer Values],"All")
RETURN
SWITCH(true(),
SelectedFilter="Remained Amber", SUMX(FILTER('Table',[Comparison]="Remained Amber"),[Column]),
SelectedFilter="Remained Red",SUMX(FILTER('Table',[Comparison]="Remained Red"),[Column]),
SelectedFilter="Other", SUMX(FILTER('Table',[Comparison]="Other"),[Column])
)

For the second part of the question, yes but not using SELECTEDVALUE, you would instead need something like:

Comparison Filter =
VAR SelectedFilter = SELECTCOLUMNS('Slicer Table',"SlicerValues",[Slicer Values],"All")
RETURN
SWITCH(true(),
"Remained Amber" IN SelectedFilter, SUMX(FILTER('Table',[Comparison]="Remained Amber"),[Column]),
"Remained Red" IN SelectedFilter,SUMX(FILTER('Table',[Comparison]="Remained Red"),[Column]),
"Other" IN SelectedFilter, SUMX(FILTER('Table',[Comparison]="Other"),[Column])
)

Again, hard to be exact. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[paul10]

@Greg_Deckler  Thank you for your reply. I get this error with your approach:

Error Message:
MdxScript(Model) (254, 116) Calculation error in measure 'Other Table'[Comparison Filter]: The function SUMX cannot work with values of type String.

All my columns and measures are of type text, i do not have any numbers to calculate. 

[Greg_Deckler]

@paul10 - Why sample data is important. Try a different aggregator like COUNTX instead of SUMX.