cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Remove filter from column but keep filter from row in matrix

Hi,

I have two tables linked by "Project Name". I put [Count Project] in the matrix with "Company" on the row and "Lv2" on the column.

 

pbix:

https://drive.google.com/file/d/1fOSTJfkfMpbK1nqa5W75cJ9L4DvENyWq/view?usp=sharing 

 

To highlight all values of our client (including blank, so I add +0 in [Count Project]), I use [Our Client Color] to conditionally formatting the background of the [Count Project].

 

In [Our Client Color], the REMOVEFILTERS ( 'Levels' ) is to remove filter from the column, otherwise "CCC crossing Mid" will not be highlighted. However, with this REMOVEFILTERS, DDD is highlighted as well.

 

I've tried many ways such as using TREATAS but still can't work. Any helps? Thanks!

 

Count Project :=
CALCULATE ( COUNT ( Main[Project Name] ) ) + 0
Our Client Color :=
VAR isOurClient =
    CALCULATE (
        [Count Project],
        Main[OurClient] = TRUE (),
        REMOVEFILTERS ( 'Levels' )
    )
RETURN
    IF ( isOurClient"Yellow""" )

matsu_0-1601019384317.png

 

without REMOVEFILTERS

matsu_0-1601027015564.png

with REMOVEFILTERS 

matsu_1-1601019427032.png

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @matsu ,

According to my understand, you want to highlight the blank value of AAA and CCC as well, right?

 

Just use  REMOVEFILTERS('Levels'[Lv2]), then the visualization looks right like this:

9.28.3.1.png

Did I answer your question? Please mark my reply as solution ,thank you.

If you have any questions, please upload some insensitive data samples .

 

Best Regards,

Eyelyn Qin

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Hi @matsu ,

According to my understand, you want to highlight the blank value of AAA and CCC as well, right?

 

Just use  REMOVEFILTERS('Levels'[Lv2]), then the visualization looks right like this:

9.28.3.1.png

Did I answer your question? Please mark my reply as solution ,thank you.

If you have any questions, please upload some insensitive data samples .

 

Best Regards,

Eyelyn Qin

View solution in original post

Super User IV
Super User IV

@matsu , Not very clear on the need to removefilter level .Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Try color measure like

if(max(Main[OurClient]) = TRUE (), "yellow", blank())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

Without the removefilter, the blank value (zero) of AAA and CCC in the matrix will not be highlighted by the color measure. I tried your color measure but it showed error. I attached the sample file with the link. Thanks.

@amitchandak, Without the removefilter, the blank value (zero) of AAA and CCC  in the matrix can't be highlighted by the color measure. I tried the measure you provided but showing errors. I attached the sample with the link. Thanks. 

matsu_0-1601023214071.png

 

 

pbix: 

https://drive.google.com/file/d/1fOSTJfkfMpbK1nqa5W75cJ9L4DvENyWq/view?usp=sharing 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors