Get Filtercontext of Visual when value is missing

[RootOfMinusOne]

Hi

Is there a way to get the filtercontext of a cell in a Matrix Visual, even if  there is no value for that  cell in the fact table?

Example, I have a table called "Data" with columns Rank, Deparment and an integer. I have the following Measure:

Beverage Consumed = coalesce(SUM(Data[Vodka Consumed]),0)

This is what the data table looks like

As you can see, the poor Privates does not get any beverage and there is no datarow with a zero in it, The zeros in the "private" column are added by the measure by using the coalesce function.

I get the colors from a separate table called "Colors" that has a row for every possible combination of Rank and Department, even for Privates. The Colortable and the Data table are NOT related.

In order to  use the color from this Color table in the first "Beverage" matrix I have created the following measure

Color = CALCULATE(MAX(Colors[Color]),TREATAS(VALUES(Data[Rank]),Data[Rank]),TREATAS(values(Data[Department]),Colors[Department]))

 (And then I have used this color-measure as Conditinal Formatting of the background color on the berage Measurge)

As you can see, this color measure will only work for the cells in the Data matrix where data exists. The private column does not get a color but this is what I want to fix

This is because I am using VALUES in the TREATAS-function but the cells of the Private column does not contain any actual data so of course I cannot use VALUES to get the filter context of the cell in this case. 

So my question is,  is it possible to define the Color measure in such a way that it will color every cell in the data table, even the cells where data is missing?

E.g., is it possible to get the filter context  of the visual without using the VALUES function? What I would need is an ALLSELECTED kind of function but for the context of the visual, not the context of slicers.

(I know there are other solutions to this problem, like moving the Rank and Department columns to a separate tables and then creating relations from those external tables to both the Data and the Color tables, or just adding rows with Zero in them to the data table. But I rather not do that I'd like to solve the actual filter context problem)

My example .pbix file is found here:

https://drive.google.com/file/d/122bowkKAeVoJjGYkRe1BRB2xU6jswfeQ/view?usp=sharing 

[BarnabasToth · Accepted solution]

You can use this measure instead: 

Color formatting = 
VAR DepartmentFilters = FILTERS ( Data[Department] )
VAR RankFilters = FILTERS ( Data[Rank] )
RETURN
CALCULATE (
    MAX( Colors[Color] ),
    TREATAS( DepartmentFilters, Colors[Department] ),
    TREATAS( RankFilters, Colors[Rank] )
)

Download the sample file here. 

This article is really helpful for a scenario you described.

Mark this answer a solution if it resolved your issue.

Regards

[johnt75]

You could use LOOKUPVALUE with SELECTEDVALUE, e.g.

Color = 
var department = SELECETDVALUE('Data'[Department])
var rank = SELECTEDVALUE('Data'[Rank])
return LOOKUPVALUE( 'Colors'[Color], 'Colors'[Department], department, 'Colors'[Rank], rank )

[RootOfMinusOne]

I appreciate the help but I tried  with the following measure

Color2 =

var department = SELECTEDVALUE('Data'[Department],"")

var theRank = SELECTEDVALUE('Data'[Rank],"")

var theCountry = SELECTEDVALUE('Country'[Country Name],"")

return LOOKUPVALUE( 'Colors'[Color], 'Colors'[Department], department, 'Colors'[Rank], theRank,Colors[Country],theCountry )

and got the exact same result as with TREATAS and VALUES, which is not that weird considered that SELECTEDVALUE is just syntatic sugar for " IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)."