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 all records for each category. If indicator 1 = "2" and indicator 2 <> "678", then the output should be the value of name 1 - for the category. Else, if indicator 1 = "3" and indicator 2 <> "678", then the output should be the value of name 2, otherwise blank.

 

Please can someone help?

 

@amitchandak @lbendlin @Greg_Deckler @Data-Rainer @tamerj1 @ppm1 @cassidy

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

     

6 Replies

  • rishirajdeb's avatar
    rishirajdeb
    Advocate I
    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

     

  • olgad's avatar
    olgad
    Resident Rockstar
    3 years ago

    Hi, 
    create a column:

    Column = if('Table'[Indicator 1]="2" &&'Table'[Indicator 2]<>"678", 'Table'[name1],if('Table'[Indicator 1] = "3"&& 'Table'[Indicator 2] <> "678", 'Table'[name2], blank()))
    create a summarized table: 
    grouped = SUMMARIZE('Table','Table'[Category], "New Column", max('Table'[Column]))

    Look up from that table: 
    Column 2 = LOOKUPVALUE(grouped[New Column],grouped[Category],'Table'[Category])



     

    • rishirajdeb's avatar
      rishirajdeb
      Advocate I
      3 years ago

      Thanks for the response, however don't think it would work as this would not iterate for each category I think. For example, a category may have multiple records satisfying both the conditions, then we will end up having multiple values for the calculated column, for the same category.

      • olgad's avatar
        olgad
        Resident Rockstar
        3 years ago

        Then please make a snapshot with the example where there are multiple values, what shall be the output. If in Category A there are AA and BB which met the condition which one shall be assigned to category A?

  • 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).