Forum Discussion

Dellis81's avatar
Dellis81
Icon for Post Prodigy rankPost 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 financi...
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

     

MFelix's avatar
MFelix
Icon for Super User rankSuper 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
    Icon for Post Prodigy rankPost 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
    Icon for Post Prodigy rankPost 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
      Icon for Super User rankSuper 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. 

      • Dellis81's avatar
        Dellis81
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        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 🙂