MATRIX PREVENT DRILL DOWN

[Dellis81]

Hello!

I am attempting to develop financial statements using the matrix visual - and mostly successful.   I am using the switch statement to pull in detail vs summary subtotals.   Within the financial statement table - I have a column - Show Detail, which is a toggle of whether the report line is allowed a drill down or not.

The left display shows the proper detail drill down and subtotals.   The right display is what we get when the user clicks on the + on the subtotal line.    Is there a way to disallow drilldown, but yet maintain the value.

I'm sure it's related to the "blank" within the "then" component of the if statement below.   Are there other options to get around the if

VAR NRSwitch =
SWITCH (
SwitchValue,
"100", -ExtNR,
"130", -FamilyNR,
"120", -RelatedPartyNR,
"140", -(FamilyNR+RelatedPartyNR),
"150", TotalNR,
BLANK ()
)

VAR CenterDescrOrig = ISFILTERED('CenterSetup (orig)'[Description])
RETURN

IF (
AND ( MAX ( NoteReceivables[Show Detail] ) = 0, CenterDescrOrig ),
blank(),
NRSwitch)

[MFelix · Accepted solution]

Hi @Dellis81 ,

In this case your calculation is correct you only need to go to the matrix options and turn on subtotals on lines. No need for additional code on your measure.

Any further help please tell me.

[MFelix]

Hi @Dellis81 ,

You can add a new level to your filtering something similar to this:

VAR NRSwitch =
SWITCH (
SwitchValue,
"100", -ExtNR,
"130", -FamilyNR,
"120", -RelatedPartyNR,
"140", -(FamilyNR+RelatedPartyNR),
"150", TotalNR,
BLANK ()
)

VAR CenterDescrOrig = ISFILTERED('CenterSetup (orig)'[Description])
RETURN
IF (HASONEFILTER(Table[Column]),
IF (
AND ( MAX ( NoteReceivables[Show Detail] ) = 0, CenterDescrOrig ),
blank(),
NRSwitch), NrSwitch)

The Table[Column] is the name that you use for the lowest detail of your matrix (cannot see what is the name of that column).

[Dellis81]

@MFelix 

Good morning MFelix

I've been working on this issue - and put together a small dataset together w/example to provide.   But, as my normal luck goes - your suggestion worked (in my small dataset).     However, I dropped the same measure back into the larger dataset - resulting in same blank result.   So I know your suggestion works, but unsure why it's not in the live file.    The only difference I know of - is my larger datasets has many more tables and relationships.   I have double/trippled checked the relationships I have in my small test file matches the same relationships in live file.

Do you have any thoughts where I might go from here?

Thanks

[MFelix]

Hi @Dellis81,

Measures are based on context so depending on the way you have the model setup and also the visualization you may need to adjust the measure. 

The formula is based on the lowest level of the matrix visualization so you need to refer to that column. 

Has you refer the formula works but it's on a different dataset so without any details on your current dataset is difficult to give you guidance. 

[Dellis81]

Hello @MFelix 

Ok, I've spent most of the day trying to prep a larger scale example, that actually mimics my live datafile.   Follow link to download     https://1drv.ms/u/s!AmBVCme14p7xlTYmh0oHTxJC1wrG?e=aJ6Csf

I have changed the example - but the concept is the same.   The items hilighted in yellow represent subtotals - and as illustrated - when click on the + symbol - the values turn into blanks.   I am hoping to retain the values.   In my rresearch - I found an example of someone using measures for the isfiltered and hasonefilter expression - so I have built those into the formula - thinking flexibility in the calculation depending on which dimension is used in rows.   The result before and after resulted in the blank.

 After a lengthy set of code to derive the a prior year average column - below is the section of measure I think we are dealing with.  If you wish to test without the filtered measures - delete the measure, and format the commented out DAX to fit your needs.

RETURN

IF([MaxtrixHasOneFilter], //HASONEFILTER(COASetup[Report LineDescription]),
IF (
AND ( MAX ( ISHeader[Show Detail] ) = 0, [MaxtrixIsFiltered]),//isfiltered(COASetup[Report LineDescription])),
blank(),
CYrorAvgColumn
),CYrorAvgColumn)

Again -= thank you.  Forcing myself to create a scaled down test model will be valuable longer term.   I have challenges to post - so if you see something we aren't discussing - that is yet to come 🙂

[MFelix]

Hi @Dellis81 ,

In the matrix visualization you don't have the option of understanding if the line is expanded or not, meaning that when you turn off the sub-totals, when you expand one of the lines the sub-total does not show.

Whit your mockup I turn on the subtotals and was abble to have the values for all the lines.

Tell me one thing what you want is to show the total when there is no detail lines, and on the other no subtotals correct?

[Dellis81]

@MFelix 

Oh, thank you very much!  :))))

Yes, if you can make subtotals show up for both drill down lines as well as total lines - that would be great!!  See yellow below

I do truly appreciate your time and assistance!  Thank you!

[MFelix · Accepted solution]

Hi @Dellis81 ,

In this case your calculation is correct you only need to go to the matrix options and turn on subtotals on lines. No need for additional code on your measure.

Any further help please tell me.

[Dellis81]

Oh, I should have thought of that - so simple.   But - I shut the row subtotals off for a reason - to avoid the "Total" line at bottom of report.  Is there a way to avoid this report line - see red circled. 

thanks

[MFelix · Accepted solution]

Hi @Dellis81 ,

There is a Sub-totals option by row the level that you need, in your case IS Header is turn off

[Dellis81]

@MFelix 

Thank you for your help on this.    Your dax solution and advice to turn on subtotals has helped alot.   I am still fighting thru various visuals - but for now will close this case.   People like you is what makes this forum great!  Thank you!

[Dellis81]

Thank you for your response 🙂

The lowest value of the matrix is 'CenterSetup (orig)'[Description].   I inserted the proper

 table[column] per your suggestion resulting in the below (partial) measure.   Unfortunately - I still get a blank when I drill down.

VAR CenterDescrOrig =
        ISFILTERED ( 'CenterSetup (orig)'[Description] )
    RETURN
        IF (
            HASONEFILTER ( 'CenterSetup (orig)'[Description] ),
            IF (
                AND ( MAX ( NoteReceivables[Show Detail] ) = 0, CenterDescrOrig ),
                BLANK (),
                NRSwitch
            ),
            NRSwitch
        )

 I have verified the 'CenterSetup (orig)'[Description] is the lowest level in matrix.

Again - thank you for your help, this forum is great because of people like you!