Forum Discussion

Salauat's avatar
Salauat
Regular Visitor
5 years ago

New column calculated by grouping

Hello gents,

I have a table, which contains few columns, which I use to drill through visualizations.

I want to create a new calculated column, which will be looking into "A1" and "Status".

Can you help me to write DAX expression for new column? 

 

Calculated logic as follows:

  • If Status is "Good" for all same A1 groups, then the Expected result "Good"
  • If at least one row in Status shows "Not Good" in one A1 grouping, then the expected result would be "Not Good"
A1StatusExpected result
1GoodNot Good
1Not GoodNot Good
1GoodNot Good
2GoodGood
2GoodGood
3Not GoodNot Good
3GoodNot Good
4Not GoodNot Good

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi,

     

    NewColumn =
REPT (
    "Not ",
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Status] = "Not Good",
        'Table'[A1] = EARLIER ( 'Table'[A1] )
    ) > 0
) & "Good"

     

    Regards

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    Expected result CC : =
    VAR _currentAone = 'Table'[A1]
    VAR _filtertable =
    FILTER ( 'Table', 'Table'[A1] = _currentAone )
    VAR _selectstatuscolumn =
    SELECTCOLUMNS ( _filtertable, "@status", 'Table'[Status] )
    RETURN
    SWITCH (
    TRUE (),
    "Not Good" IN _selectstatuscolumn, "Not Good",
    NOT ( "Not Good" IN _selectstatuscolumn ), "Good"
    )
     
     
     
  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago
    Flag =
IF(
    ISEMPTY(
        FILTER( INFO, INFO[A1] = EARLIER( INFO[A1] ) && INFO[Status] <> "Good" )
    ),
    "Good",
    "Not Good"
)