Forum Discussion

ab00sa7i's avatar
ab00sa7i
Frequent Visitor
2 years ago
Solved

Help with DAX

Hi, I have a data looks like this:

 I would like to create a three new columns (like shown on the right of the picture),

 

the first column should contain the first reason of error (type of error according to importance, should be number 3 as it is the most important).

 

Second column should contains the second reason of error ( should be number 2)

 

And thrid column should contains the third reason.

 

https://docs.google.com/spreadsheets/u/0/d/1Irl4Pp3o8BXTPxwRm1tuT5hdGwO2U-OP/htmlview

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ab00sa7i ,

    I create the table as you mentioned.

    Then I think you can create three calculated columns.

    reason1 = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _max
    )
    reason2 = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
VAR _next =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[number of repeted error with important degree] < _max
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _next
    )
    reason3 = 
VAR _min =
    CALCULATE (
        MIN ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _min
    )

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

    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 ab00sa7i ,

    I create the table as you mentioned.

    Then I think you can create three calculated columns.

    reason1 = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _max
    )
    reason2 = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
VAR _next =
    CALCULATE (
        MAX ( 'Table'[number of repeted error with important degree] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[number of repeted error with important degree] < _max
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _next
    )
    reason3 = 
VAR _min =
    CALCULATE (
        MIN ( 'Table'[number of repeted error with important degree] ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[error type] ),
        ALLSELECTED ( 'Table' ),
        'Table'[number of repeted error with important degree] = _min
    )

    Finally you will get what you want.

     

     

     

    Best Regards

    Yilong Zhou

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