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

     

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?