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
WinterMist
Impactful Individual
Impactful Individual

DAX Intentional BLANK Total Is Overridden by Visual Filter

Hello Community -

 

In the following table visual, the totals for the following measures are intentionally blank.

- Accounts 

- Retained

- Retained %

 

WinterMist_0-1680200767072.png

 

These 3 measures (renamed) show BLANK for the Total row by using ISFILTERED on the Year.

 

WinterMist_1-1680200946659.png

 

WinterMist_2-1680201048292.png

 

WinterMist_3-1680201305841.png

 

So far so good.

 

The problem comes when I need to only show rows in the table visual where CY Premium > 0.

For example, 2024 is currently showing in the table visual, but should not appear until CY Premium > 0.

 

To achieve this, the following visual filter is created.

 

WinterMist_4-1680201753390.png

 

GOOD - This filter correctly hides the 2024 row because CY Premium is not greater than 0 in that year. 

BAD - However, doing so breaks the intentional BLANKING of the 3 measures mentioned at the beginning of this post.

 

WinterMist_0-1680202896078.png

 

How do I get both requirements to work at the same time?

I thought about doing the ISFILTERED on a different column in the table.

The problem is that every column in the table visual (except Year) is just another measure which cannot be used in the ISFILTERED clause to BLANK a measure in the totals row.

 

Any guidance would be greatly appreciated.

 

Regards,

Nathan

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @WinterMist 

you can use HASONEVALUE 

View solution in original post

2 REPLIES 2
WinterMist
Impactful Individual
Impactful Individual

@tamerj1 - Thank you, yet again!  Helpful as always.

tamerj1
Super User
Super User

Hi @WinterMist 

you can use HASONEVALUE 

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.

Top Solution Authors