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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Raveeshlokanath
Frequent Visitor

Want to avoid dimension to get Total

Hi All,

 

I want to exclude the marked dimension and get the total as 42413 as markd in snap

 

Raveeshlokanath_0-1718963444901.png

 

please give your thoughts, I tried with 

CALCULATE([TRs],ALL(QLTY_TR_DETAILS_All_Padded)) but it excludes all fiters and gives more value, I want to exclude first column dimension only.
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Raveeshlokanath,

You can add a if statement to check the current row contents level with isinscope function and replace not matched levels.

formula =
IF ( ISINSCOPE ( QLTY_TR_DETAILS_All_Padded[Category] ), [TRs] )

Clever Hierarchy Handling in DAX - SQLBI

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Raveeshlokanath,

You can add a if statement to check the current row contents level with isinscope function and replace not matched levels.

formula =
IF ( ISINSCOPE ( QLTY_TR_DETAILS_All_Padded[Category] ), [TRs] )

Clever Hierarchy Handling in DAX - SQLBI

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
mark_endicott
Super User
Super User

@Raveeshlokanath - it looks like you have named the whole dimension table in your ALL( ) rather than just the column, you can try:

 

CALCULATE([TRs],ALL('QLTY_TR_DETAILS_All_Padded'[Column]))

 

Just replace the word Column in my code with the column in your visual. 

 

If you are applying a filter to that column already, you may need to use ALLSELECTED( ) instead.

 

If this works, please accept as a solution. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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