Displaying/Hiding Measures in Hierarchies

Power BI Data Model
1

I have a situation where I have a sales budget at a branch level but not a business segment level. I created a matrix with the hierarchy of branch then business segment. I want to display the budget when the matrix is at it’s highest level (branch) but hide the budget column when a user drills to the next level in the hierarchy (business segment). This is complicated by the fact that we have a business segment slicer. I want to display the budget when there is no business segment selected in the slicer but hide the budget when a user selects a business segment.

What DAX expression would I use to enable this behavior?

2

So you would have to use logic that incorporate ISFILTERED or ISCROSSFILTERED in there. Then this likely can be solved.

I would need to see more to get the correct formula though. Ie. The model, current formulas, the table you want to showcase in etc.

There’s certainly a bit to this one so the more information the better.

Have you used (IF( ISFILTERED… logic before?

3

Finance%20Sales%20Data%20Model
Finance Sales Data Model.PNG941×869 32.9 KB

finance%20sales%20matrix
finance sales matrix.PNG1458×451 45.6 KB

Hi Sam,

Thank you for the response. I have never used IF(ISFILTERED(…)) logic before. The data model is rather complicated as I am calculating a few different revenue related KPI’s on the same report. This tab of the report focuses on sales growth and each profit center has a goal based on the percent change of YTD sales from LYTD sales.
In the matix I drilled to single location to show the hierarchy (1. Profit Center 2.Business Segment) and the issue. We want to display the sales goal at the highest level in the hierarchy but show blank() for the second level of the hierarchy. The formula for the goal is rather simple right now:

Sales Goal 2018 = if(
                                 sum('FinanceGoals2018'[2018 Revenue Goal])=0
                                ,BLANK()
                                ,iferror(
                                      sum('FinanceGoals2018'[2018 Revenue Goal])/sum('FinanceGoals2018'[2017 
                                      Revenue])-1
                                     ,BLANK()
                                           )
                                  )
I just tried this formula but the entire column became blank:
Sales Goal 2018 = if(ISFILTERED(GL_Structure[Segment])
                    ,BLANK()
                    ,if(
                        sum('FinanceGoals2018'[2018 Revenue Goal])=0
                        ,BLANK()
                        ,iferror(
                                sum('FinanceGoals2018'[2018 Revenue Goal])/sum('FinanceGoals2018'[2017 Revenue])-1
                                ,BLANK()
                                )
                        )

Any help you can provide would be great.

Thank you Sam!

4

Hi Sam,

I actually got this to work. My problem was that the highest level of the hierarchy was a profit center (fact attribute) rather than branch (dimension attribute). My goals are all at the branch level so I replaced profit center with branch the formula worked.

Thank you!

5

Hi Casey before I forget, see here for how to get better formatting on DAX formulas within posts

https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156

6

Ok great that you got it sorted.

Yes your model looks a little complex to me.

Can you simplify it with some of the best practices from here?

7

Hi Sam,

Thanks for pointing me in the right direction for data modeling in Power BI. I will review the course videos and see what I can do to simplify the data model.

Thank you,