Forum Discussion
Post ProdigyMATRIX PREVENT DRILL DOWN
Super UserHi 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).
Post Prodigy
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?