ISINSCOPE in ROW Subtotal Level

[smpa01]

@AlexisOlson this is a follow-up question to this topic 

I have come up with this measure but it is not getting reflected on the Row Subtotal Level. On Row Subtotal Level, the ISINSCOPE is failing

_measure = 
var _country_scope = ISINSCOPE(dim_country[dim.country])
var _region_scope = ISINSCOPE(dim_region[dim.region])
var _subregion = ISINSCOPE(dim_subregion[dim.subregion])

var _baseTableSubRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]),ALLSELECTED(dim_subregion[dim.subregion]))
var _baseTableRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]))
var _baseTableCountry = ALLSELECTED(dim_country[dim.country])
var _conditional = SWITCH (
    TRUE (),
   _subregion,SUMX(ADDCOLUMNS(_baseTableSubRegion,"value",[value]),[value]),
   _region_scope, SUMX(ADDCOLUMNS(_baseTableRegion,"value",[value]),[value]),
    _country_scope,SUMX(ADDCOLUMNS(_baseTableCountry,"value",[value]),[value])
)
RETURN _conditional

Thank you in advance

[amitchandak · Accepted solution]

@smpa01 , Try a measure like

_measure = 
var _country_scope = ISINSCOPE(dim_country[dim.country])
var _region_scope = ISINSCOPE(dim_region[dim.region])
var _subregion = ISINSCOPE(dim_subregion[dim.subregion])

var _baseTableSubRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]),ALLSELECTED(dim_subregion[dim.subregion]))
var _baseTableRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]))
var _baseTableCountry = ALLSELECTED(dim_country[dim.country])
var _conditional = SWITCH (
    TRUE (),
   _subregion,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableSubRegion,dim_country[dim.country],dim_region[dim.region],dim_subregion[dim.subregion]),"value",[value]),[value]),
   _region_scope, SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableRegion, dim_country[dim.country], dim_region[dim.region]),"value",[value]),[value]),
    _country_scope,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableCountry, dim_country[dim.country]),"value",[value]),[value])
    ,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableCountry, dim_country[dim.country]),"value",[value]),[value])
)
RETURN _conditional

[AlexisOlson · Accepted solution]

I don't understand why you have 3 geography dimension tables rather than conforming them into a single dimension table.

The reason your total is blank is because all of the scope checks fail and you haven't defined a fallback calculation in your SWITCH. One potential fix for that:

VAR _conditional =
    SWITCH (
        TRUE (),
        _subregion,     SUMX ( _baseTableSubRegion, [value] ),
        _region_scope,  SUMX ( _baseTableRegion,    [value] ),
        _country_scope, SUMX ( _baseTableCountry,   [value] ),
        [value]
    )

I also don't understand what the purpose of your measure is ultimately supposed to be. If you always want the total across all geographies, you can write something much simpler like this as your entire measure:

CALCULATE (
    [value],
    ALLSELECTED ( dim_country ),
    ALLSELECTED ( dim_region ),
    ALLSELECTED ( dim_subregion )
)

[amitchandak · Accepted solution]

@smpa01 , Try a measure like

_measure = 
var _country_scope = ISINSCOPE(dim_country[dim.country])
var _region_scope = ISINSCOPE(dim_region[dim.region])
var _subregion = ISINSCOPE(dim_subregion[dim.subregion])

var _baseTableSubRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]),ALLSELECTED(dim_subregion[dim.subregion]))
var _baseTableRegion = CROSSJOIN(ALLSELECTED(dim_country[dim.country]),ALLSELECTED(dim_region[dim.region]))
var _baseTableCountry = ALLSELECTED(dim_country[dim.country])
var _conditional = SWITCH (
    TRUE (),
   _subregion,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableSubRegion,dim_country[dim.country],dim_region[dim.region],dim_subregion[dim.subregion]),"value",[value]),[value]),
   _region_scope, SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableRegion, dim_country[dim.country], dim_region[dim.region]),"value",[value]),[value]),
    _country_scope,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableCountry, dim_country[dim.country]),"value",[value]),[value])
    ,SUMX(ADDCOLUMNS(SUMMARIZE(_baseTableCountry, dim_country[dim.country]),"value",[value]),[value])
)
RETURN _conditional

[smpa01]

Thanks for looking into this. I need to compare this with my PROD material and let me come back to you in a day or two with any follow-up question I might have.