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

HIde column data in lower hierarchy of Matrix

I've got a matrix with 3 levels of hierarchy:  Employee > Project > Phase.  

gdaniel_3-1633116859769.png

 

Each Project has a Cost, which which is displayed in the cost column when drilled down to the second (Project) level of the hierarchy.  

gdaniel_1-1633116529188.png

 

but when I drill down to the third level in the hierarchy (Phase), I don't want the cost data to show because it is not relevant to that row.

gdaniel_2-1633116559823.png

 

I've tried several options with no luck so far.  I'm rather new at Power BI, so any help is appreciated.  Thanks!

 

1 ACCEPTED SOLUTION
richbenmintz
Super User
Super User

Hi @gdaniel,

In your measure you need to identify if the third level is being shown, the following should work

 

construction cost =
switch(true(),
hasonevalue('table'[phase]), blank(), // returns blank() if phase is displayed with a single value in the matrix
sum(table[constructionCost])// default condition
)

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

9 REPLIES 9
richbenmintz
Super User
Super User

Hi @gdaniel,

In your measure you need to identify if the third level is being shown, the following should work

 

construction cost =
switch(true(),
hasonevalue('table'[phase]), blank(), // returns blank() if phase is displayed with a single value in the matrix
sum(table[constructionCost])// default condition
)

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

Thanks for the quick reply Richard!  Not sure whats going on under the hood, but it did something weird - inactive projects are now showing, some with 0 cost, and the 3rd level of the hierarchy (Phase) is no longer visible as a drill down level.   Might be something with the way my table are joined perhaps.  

gdaniel_0-1633124007165.png

 

Hi @gdaniel ,

 

Can you provide a sample pbix



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


When I went to create the sample pbix, I put the data into a csv file to send.  However your code works great with the csv file as the data source.  Which makes me think it may be something with my table joins that is causing the measure to not work as expected.   I will troubleshoot from that angle and see if I can figure it out.  Again, thanks for all your assistance

 

MFelix
Super User
Super User

Hi @gdaniel,

 

Try to wrap your measure in a IF statement with ISINSCOPE syntax something similar to

 

COSTS = IF(ISINSCOPE(TABLE[Phase]), BLANK(), SUM(TABLE[Cost])) 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



gdaniel
Frequent Visitor

Thanks for your help!

Hi @gdaniel ,

 

Just a heads up regading the calculation that @richbenmintz  gave you.

 

Using the HASONEVALUE function will check if the values you have at phase level is a single value so if you have hierarchy where Employee -> Project -> phase has a single phase the value will be zero for all level no matter what is the level you are in check the example below:

 

MFelix_1-1633362812102.png

 

If you look at the matrix has you can see above there are 3 projects that don't show value at the HASONEVALUE measure:

A-z-a

b-x-a

d-v-y

The detail is checked on the two matriz the right

 

This is happening because based in context the calculation is getting a single phase for each one so the level will not return any value but blank.

 

The context on the calculation of measures is very important and in this case the fact that you only have one value on your phase can influence the rest of the calculations.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix ,

 

I did not change the context of the value based on the level of the Hierarchy being displayed, only provided the initial true() condition for the switch.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz,

 

You are correct that you did not changed it however what you are checking is:

IF the Phase has one value then return blank  if not return costs

 

While doing this the context of your measure goes line by line on the matrix and checks for that line if the number of phase is only one when you do that it will return blank no matter what level you are in, my example was done with you formula.

 

Context in DAX is very sensitive and when you have implicit context on your calculations (filters, slicers, matrix rows or columns and so on) your attention needs to be double.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors