Data Aggregation from live data

[Anonymous]

Hi Everyone,

I have a data aggregation question. My data is in following shape (this is just a sample of data for the large dataset)

Type	Level 4	Level 3	Level 2
Type1	10432	112808	45688
Type1	10432	112808	45688
Type2	10438	104276	703104
Type3	254	945042	250782
Type3	254	945042	312345
Type3	254	786648	124294
Type4	165	677827	390093
Type4	165	1170683	652497

I want to develop a DAX formula to develop the result column. I have updated the type column for ease of understanding. Essential the result column segeragate the following types on the basis of two option - Standalone & multi hierarchy. Standalone are those types where unique combination of level 4,3,2 has occured once. Hence, type 2 is standalone. In type 1, we have same combination of of level 4,3,2 & hence it is also standalone. For multi Hierarchy, there must be atleast two combination possible at level 4. As there are atleast 2 condition for each of these rows, hence type 3 & 4 qualify for multi Hierarchy. 

Please help with the DAX code to aggregate each result at level 4. 
The table will look like this 

Type	Level 4	Level 3	Level 2	Result
Type1	10432	112808	45688	Standalone
Type1	10432	112808	45688	Standalone
Type2	10438	104276	703104	Standalone
Type3	254	945042	250782	Multi Hierarchy
Type3	254	945042	312345	Multi Hierarchy
Type3	254	786648	124294	Multi Hierarchy
Type4	165	677827	390093	Multi Hierarchy
Type4	165	1170683	652497	Multi Hierarchy

[v-xinruzhu-msft · Accepted solution]

Hi  @Anonymous 

If you want to create calculated column,you can refer to the following code.

Result =
VAR a =
    SUMMARIZE (
        'Table',
        [Type],
        'Table'[Level 4],
        'Table'[Level 3],
        'Table'[Level 2]
    )
VAR b =
    COUNTROWS ( FILTER ( a, [Type] = EARLIER ( 'Table'[Type] ) ) )
RETURN
    SWITCH ( TRUE (), b = 1, "Standalone", b > 1, "Multi Hierarchy" )

Output

If you want to create measure,you can refer to the following code.

Measure =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Type],
        'Table'[Level 4],
        'Table'[Level 3],
        'Table'[Level 2]
    )
VAR b =
    COUNTROWS ( FILTER ( a, [Type] IN VALUES ( 'Table'[Type] ) ) )
RETURN
    SWITCH ( TRUE (), b = 1, "Standalone", b > 1, "Multi Hierarchy" )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.