DAX measure to work based on ISINSCOPE of matrix visual

[anuj_gupta487]

Hi Everyone,

I am trying to write a DAX for a matrix visual, that will be used in Values section in visualization pane, and matrix has a heirarchy for ManagedGroup and UserName (will go in Rows in visualization pane).
I need to show the DistinctCount of AccountId at user level but at group level, is should be sum of user level values.

So based on below sample data, it should show

Admin - 4 (2 + 2)

Lead - 4 (1+ 2 + 1)

Here is the sample data:

ManagedGroup	UserName	AccountId	DateActionTaken
Admin	Aaron	efgh	2/28/2023
Admin	Aaron	efgh	4/23/2023
Admin	Aaron	ghij	5/15/2023
Admin	John	cdef	6/16/2023
Admin	John	efgh	3/15/2023
Lead	Brock	abcd	5/16/2023
Lead	Brock	abcd	3/24/2023
Lead	Cathy	abcd	6/27/2023
Lead	Cathy	ghij	5/11/2023
Lead	Derek	ijkl	4/9/2023

Matrix visual:

For Group Level, it is recalculating the DISTINCTCOUNT of AccountId, so the group level values are
Admin - 3
Lead - 3

Also in ROW Grand Total -> again need to show sum of values at group level, so that would be 8 (currently showing as 5)

ItemsNew Measure definition: 

ItemsNew =

VAR _isgroup = NOT(ISINSCOPE(Sheet3[UserName]))

VAR _items =

    IF(_isgroup,

        COALESCE(DISTINCTCOUNT(Sheet3[AccountId]),0),

        COALESCE(DISTINCTCOUNT(Sheet3[AccountId]),0)

    )

return _items

------------------------------------------------------------------------------

I was trying to use SUMMARIZE and do the sumx for group level, but it will return the table not scalar value and not able to reference the table column created in SUMMARIZE for any other DAX function
SUMX(
SUMMARIZE(Sheet3[UserName], Sheet3[UserName], "NewColumn", DISTINCTCOUNT(Sheet3[AccountId])),
"NewColumn"
)

I added DateActionTaken because user will have external page level filter and based on the date filter, the values needs to change, so cannot summarize the data in underlying table

Could you please help me with that
  

[anuj_gupta487 · Accepted solution]

I was able to resolve it.
Using this DAX in the measure now:
ItemsNew =  
VAR _isgroup = NOT(ISINSCOPE(Sheet3[UserName]))  
RETURN  
IF(  
    _isgroup,  
    SUMX(  
        VALUES(Sheet3[UserName]),  
        CALCULATE(DISTINCTCOUNT(Sheet3[AccountId]))  
    ),  
    DISTINCTCOUNT(Sheet3[AccountId])  
)  

here the VALUES function is going to give a table with all the distinct user in that group, and then SUMX using CALCULATE will iterate over all the users and calculate the distinctcount of AccountId

Note: I was wrong about the SUMX function, it applies the calculation row by row and then sums up all the values, in the end it will return a scalar value only.