SSAS Tabular SSDT DAX Drillthrough Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2056924%22%20slang%3D%22en-US%22%3ESSAS%20Tabular%20SSDT%20DAX%20Drillthrough%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2056924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHello%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3EI've%20come%20across%20a%20scenario%20where%20the%20drillthrough%20functionality%20is%20not%20working%20when%20multiple%20dates%20from%20a%20fact%20table%20is%20joined%20to%20a%20single%20date%20dimension.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3EIf%20I%20have%20a%20fact%20table%20with%20multiple%20status%20dates%20e.g.%20Resolved%20Date%2C%20Logged%20Date%20and%20join%20it%20to%20one%20Date%20dimension%20table%2C%20one%20of%20the%20relationship%20would%20be%20made%20active%20and%20to%20get%20the%20other%20inactive%20relationship%20to%20work%20I%20use%20the%20USERELATIONSHIP%20function.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3ECALCULATE(SUM(%5BClosedFlag%5D)%2CUSERELATIONSHIP('Incident%20Metrics'%5BClosedDateSID%5D%2C'Date'%5BDateDefinitionSID%5D))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3EThe%20results%20work%20fine%20when%20selecting%20the%202%20measures%20against%20the%20one%20date%20dimension.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3EThe%20issue%20I'm%20experiencing%20is%20when%20I%20drillthrough%20the%20measure%20on%20the%20InActive%20measure%20%3CSTRONG%3Ein%20Excel%3C%2FSTRONG%3E%20by%20adding%20the%20DAX%20in%20the%20Detail%20Row%20Expression%2C%20the%20drillthrough%20return%20the%20rows%20that%20is%20from%20the%20active%20relationship%20and%20not%20from%20the%20measure%20that%20was%20from%20the%20inactive%20relationship.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CBR%20%2F%3EHow%20can%20I%20get%20the%20inactive%20measures%20to%20drillthrough%20correctly%3F%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2056924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDrillthrough%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESSAS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETabular%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello,


I've come across a scenario where the drillthrough functionality is not working when multiple dates from a fact table is joined to a single date dimension.


If I have a fact table with multiple status dates e.g. Resolved Date, Logged Date and join it to one Date dimension table, one of the relationship would be made active and to get the other inactive relationship to work I use the USERELATIONSHIP function.


CALCULATE(SUM([ClosedFlag]),USERELATIONSHIP('Incident Metrics'[ClosedDateSID],'Date'[DateDefinitionSID]))


The results work fine when selecting the 2 measures against the one date dimension.


The issue I'm experiencing is when I drillthrough the measure on the InActive measure in Excel by adding the DAX in the Detail Row Expression, the drillthrough return the rows that is from the active relationship and not from the measure that was from the inactive relationship.


How can I get the inactive measures to drillthrough correctly?

0 Replies