Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Data Aggregation from live data

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)

TypeLevel 4Level 3Level 2
Type11043211280845688
Type11043211280845688
Type210438104276703104
Type3254945042250782
Type3254945042312345
Type3254786648124294
Type4165677827390093
Type41651170683652497

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 

TypeLevel 4Level 3Level 2Result
Type11043211280845688Standalone
Type11043211280845688Standalone
Type210438104276703104Standalone
Type3254945042250782Multi Hierarchy
Type3254945042312345Multi Hierarchy
Type3254786648124294Multi Hierarchy
Type4165677827390093Multi Hierarchy
Type41651170683652497Multi Hierarchy




 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.