DAX to avoid showing measure on the drill-up level in a matrix viz

[smpa01]

Hello guys,

Is there any way to tell DAX to avoid showing measure on the drill up level.

E.g. attached is a small sacle repliction of my problem.

This is my data model.

Data Model

and this is my viz

viz

Now, when I drill up, I don't want matrix to show the measure (Descr) value . it is probably showing the first measure value which I don't want.

What I can't do,  is to use Description from T2 and use it as rows instead of using Descr measure.

Thank you in advance.

[camargos88]

Hi @smpa01 ,

Try this code:

Descr =

IF(

NOT(ISFILTERED(T1[fiscal_year])) ||

NOT(ISFILTERED(T1[period_number])); ""; CALCULATE(MAX(T2[Description])))

[amitchandak]

@smpa01 ,  I did not get it completely. See if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[smpa01 · Accepted solution]

@amitchandak thanks man for this. I learnt something new today and extremely useful for displaying selective total at the desired level of hirearchy. This is awesome mate !!!!

@camargos88 thanks but it still does not do what I had in mind.

For all future searchers , this is how the problem is resolved.

[camargos88]

@smpa01 ,

 

Descr =

VAR _desc = MAX(T2[Description])

VAR _f_periodNumber = ISFILTERED(T1[period_number])

VAR _f_WO = ISFILTERED(T1[WO])

RETURN

IF(_f_periodNumber && _f_WO; _desc; "")

[smpa01]

@camargos88 thanks again for this as I like to know multiple ways of solving the same problem.

However, there is an issue with this approach vs ISINSCOPE and in my view ISINSCOPE has an upper hand.

If the BI writer chooses to use ISFILTERED instead he needs to manually wrap all the drilldown levels inside ISFILTERED. If you foget one you will not get what you desired.

For example, if you tweak your measure  to this as you forgot to include VAR _f_WO in the codition below

Descr2 = 
VAR _desc = MAX(T2[Description])
VAR _f_periodNumber = ISFILTERED(T1[period_number])
VAR _f_WO = ISFILTERED(T1[WO])
VAR _x =IF( _f_periodNumber=TRUE(), _desc, "")
RETURN _x

you don't get what you desired

So with this approach the moment you add an additional level in th the hierarchy, you need to remember to wrap that one up in your measure. Whereas with ISINSCOPE you are telling your measure to check the existenece of only one column in the filter context VS many columns in ISFILTERED. So for this particular problem ISFILTERED is more tedious.

[camargos88]

Hi @smpa01 ,

I got your point, but I tried this code (I sent it before):

Descr =

VAR _desc = MAX(T2[Description])

VAR _f_periodNumber = ISFILTERED(T1[period_number])

VAR _f_WO = ISFILTERED(T1[WO])

RETURN

IF(_f_periodNumber && _f_WO; _desc; "")

It removes the values when them don't have filters. I aggree with you it has to be done manually.

It's nice we can achieve the same results with different approaches.