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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Matrix with multiple measures - disable drilldown only for one measure

Hello,

 

I have a matrix with three measures in the values tab and I need to disable the drill down for one of the measures. Is this possible? Thank you.

7 REPLIES 7
parry2k
Super User
Super User

@Anonymous not sure what you mean? You mean when you drilling into the rows, at next level you don't want to see the value for the measure? Provide bit more context. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hello @parry2k ,

 

I have attached an image to illustrate my situation. The Budget and Variance measures need to be displayed only at total level, and not split by account, as in the second photo.

drill.JPG

drill2.JPG

@Anonymous

Is budget and variance already a MEASURE as you say? If so please can you share the DAX formula you are currently using for these two measures? You could achieve this by focusing on the Measures totals, and add an IF statement into the measure such as :

BudgetNoDrill = IF(HASONEVALUE(Table[Account]), BLANK(), [Budget])


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hello @AllisonKennedy

 

Budget and Actual are two different measures. The formula is: B_NetRevenue =
SUMX(
FILTER(FactTable,RELATED(Account[Category3]) = "Net Revenue",[BudgetValue]
)

The actual formula is the same. For the actual I want to display the categories under net revenue, the budget needs to be displayed only at net revenue level. Thank you.

@Anonymous
What column are the categories in? You won't be able to hide the columns automatically, but you can make the measure blank. As I mentioned in my previous post, use the column that contains the category, and check that it HASONEVALUE or ISFILTERED and if that's true, display BLANK(), Otherwise do your calculation:

B_NetRevenue =
IF(ISFILTERED(FactTable[CategoryAccount]), BLANK(),
SUMX(
FILTER(FactTable,RELATED(Account[Category3]) = "Net Revenue",[BudgetValue]
)
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hello @AllisonKennedy ,  the categories are in a column under Account. In the columns part of the matrix I have two columns selected, Category3 and the other small categories which I need to show, but only for Actual, not Budget and Variance. I understand that this is the standard behaviour of the matrix, I will try what you suggested. Thank you.

Thanks for the update @Anonymous
Let us know how you get on.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.