Forum Discussion

Dellis81's avatar
Dellis81
Post Prodigy
6 years ago
Solved

MATRIX PREVENT DRILL DOWN

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)

 

 
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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's avatar
    MFelix
    6 years ago

    Hi Dellis81 ,

     

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

     

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
      Dellis81
      Post Prodigy
      6 years ago

      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!

    • Dellis81's avatar
      Dellis81
      Post Prodigy
      6 years ago

      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's avatar
        MFelix
        Super User
        6 years ago

        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.