Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
1 year ago

Manipulation of Row Level Subtotal

AlexisOlson CNENFRNL bcdobbs parry2k    I want to be able to manipulate the subtotal on a matrix visual as per the business req. The req tells me to show fact_max as it is currently visible now...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
1 year ago

It's not too hard to write a measure that works at any particular level. The problem here is that you want a measure that returns a different value at e.g. the country level depending on whether the state level is expanded or not. I suspect this may not be possible since it's essentially asking for an inconsistent definition of a measure.

 

There are possible workaround with bookmarks or maybe adding a new parameter to control what levels show up (rather than using the visual's drill up/down behavior).

 

 

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
1 year ago

I think the problem can be narrowed down to

if there is a way DAX can return which level is currently expanded in matirx, and based on that manipulate each measure. But I dont think DAX has a way to  return this info jeffrey_wang 

debugger6 = 
VAR L1 =ISINSCOPE ( dim_L1[L_1] )
VAR L2 =ISINSCOPE ( dim_L2[L_2] )
VAR L3 =ISINSCOPE ( dim_L3[L_3] )
VAR tbl =
    SWITCH (
        TRUE (),
        L1 = TRUE () && L2 =FALSE(),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL ( 'dim_L1'[L_1], "IsGrandTotalRowTotal" )
                ),
                -1
            ),
        L1 = TRUE ()
            && L2 = TRUE () && L3=FALSE(),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL (
                        'dim_L1'[L_1],
                        "IsGrandTotalRowTotal",
                        'dim_L2'[L_2],
                        "IsDM1Total"
                    )
                ),
                -1
            ),
        L1 = TRUE ()
            && L2 = TRUE ()
            && L3 = TRUE (),
            TOJSON (
                SUMMARIZECOLUMNS (
                    ROLLUPADDISSUBTOTAL (
                        'dim_L1'[L_1],
                        "IsGrandTotalRowTotal",
                        'dim_L2'[L_2],
                        "IsDM1Total",
                        'dim_L3'[L_3],
                        "IsDM3Total"
                    )
                ),
                -1
            )
    )
RETURN
    tbl

 

 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    I think ISINSCOPE can handle that but you MUST start from the bottom up, not from the top down.

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      1 year ago

      Starting from the bottom up, we can detect the current level

      current_level =
SWITCH (
    TRUE (),
    ISINSCOPE ( dim_L3[L_3] ), 3,
    ISINSCOPE ( dim_L2[L_2] ), 2,
    ISINSCOPE ( dim_L1[L_1] ), 1,
    0
)

       

      The hard part is detecting the maximum level that is displayed in the visual. If you have that, all you need is

      IF ( [current_level] = [max_level], [fact_sum] )

      But I can't see how to calculate [max_level] dynamically in a subtotal row filter context.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

        the simplistic answer would be HASONEVALUE but that can backfire badly if a parent level only has a single child.