Calculated column based on each category

[rishirajdeb]

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

[rishirajdeb · Accepted solution]

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]

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

[olgad]

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]

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]

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?

[BIswajit_Das]

Hello @rishirajdeb This may comes handy