Forum Discussion

smpa01's avatar
smpa01
Community Champion
1 year ago
Solved

ISINSCOPE in ROW Subtotal Level

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

Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    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's avatar
    AlexisOlson
    1 year ago

    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 )
)

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    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's avatar
      smpa01
      Community Champion
      1 year ago

      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.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    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 )
)