Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RootOfMinusOne
Frequent Visitor

Get Filtercontext of Visual when value is missing

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
RootOfMinusOne_0-1646389595553.png

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.

 

RootOfMinusOne_1-1646389835407.png

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 

 

 

 

 

 

 
 

 

1 ACCEPTED SOLUTION
BarnabasToth
Resolver I
Resolver I

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

View solution in original post

3 REPLIES 3
BarnabasToth
Resolver I
Resolver I

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
Super User
Super User

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 )

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>)."

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.