Forum Discussion

rishirajdeb's avatar
rishirajdeb
Advocate I
3 years ago
Solved

Calculated column based on each category

Hi All,   Need help with the logic of a calculated column:   Representative data along with expected output:       The logic of the Calculated column should be:   need to iterate over...
Need Help
  • rishirajdeb's avatar
    rishirajdeb
    3 years ago

     

     

    Just wanted to update that I have been able to make it work as expected. The DAX code snippet:

     

    CalColumn =
VAR _key = Sheet1[category]   // to check for each category
VAR _case1 =  // to check if the first condition exists, on a category level
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        ALL ( Sheet1 ),
        FILTER (
            Sheet1,
            Sheet1[category] = _key
                && Sheet1[indicator 1] = "2"
                && Sheet1[indicator 2] <> 678
        )
    )
VAR _case2 =  // to check if the second condition exists, on a category level
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        ALL ( Sheet1 ),
        FILTER (
            Sheet1,
            Sheet1[category] = _key
                && Sheet1[indicator 1] = "3"
                && Sheet1[indicator 2] <> 678
        )
    )
VAR _case1_name = // required output for the first condition
    CALCULATE (
        MAX ( Sheet1[name 1] ),
        ALL ( Sheet1 ),
        FILTER (
            Sheet1,
            Sheet1[category] = _key
                && Sheet1[indicator 1] = "2"
                && Sheet1[indicator 2] <> 678
        )
    )
VAR _case2_name = // required output for the second condition
    CALCULATE (
        MAX ( Sheet1[name 2] ),
        ALL ( Sheet1 ),
        FILTER (
            Sheet1,
            Sheet1[category] = _key
                && Sheet1[indicator 1] = "3"
                && Sheet1[indicator 2] <> 678
        )
    )
RETURN
    IF ( _case1 > 0, _case1_name, IF ( _case2 > 0, _case2_name, BLANK () ) ) // returning output based on condition

     

    Thanks

     

rishirajdeb's avatar
rishirajdeb
Advocate I
3 years ago

Let me update the sample data, which is causing confusion I guess (for category A and indicator 1 = "3", indicator 2 now has a different value than 678). The main ask here to implement the logic on the category level (not on line item level). Example, for category A, the first logical test of the IF condition is true (for the 1st line item from top), hence the entire category would be classified as "AA" (name 1). For category B, the first logical test is not true for any of the line items, hence the 2nd logical test will be performed, which is true (for the second line item from bottom), and so the entire category will be classified as "SS" (name 2).