Forum Discussion

ikibirev's avatar
ikibirev
Frequent Visitor
7 years ago
Solved

Grouping table by condition

Hello everybody! Please help with DAX. I have an unnormalized table as a data source. Like this: ID             Task       Date 1              1             01.01.2018 1              2         ...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    From what you show you seem to want two calculated columns, not measures.

    Try this for calculated columns in the table you show (Table1):

    Completed =
IF (
    CALCULATE (
        COUNT ( MainTable[Task] );
        MainTable[Task] = "Step3";
        ALLEXCEPT ( MainTable; MainTable[Doc ID] )
    ) > 0;
    "OK";
    "KO"
)
    Completed Date =
IF (
    MainTable[Completed] = "OK";
    LOOKUPVALUE (
        MainTable[Date];
        MainTable[Doc ID]; MainTable[Doc ID];
        MainTable[Task]; "Step3"
    )
)
ikibirev's avatar
ikibirev
Frequent Visitor
7 years ago

Here more full example to clarify what I want to get:

 

The Table has fields Doc ID, Task and Date, when task copleted. I want to add measures Completed (for ID) and Completed Date (for ID) which will return result as on the picture.

AlB's avatar
AlB
Community Champion
7 years ago

From what you show you seem to want two calculated columns, not measures.

Try this for calculated columns in the table you show (Table1):

Completed =
IF (
    CALCULATE (
        COUNT ( MainTable[Task] );
        MainTable[Task] = "Step3";
        ALLEXCEPT ( MainTable; MainTable[Doc ID] )
    ) > 0;
    "OK";
    "KO"
)
Completed Date =
IF (
    MainTable[Completed] = "OK";
    LOOKUPVALUE (
        MainTable[Date];
        MainTable[Doc ID]; MainTable[Doc ID];
        MainTable[Task]; "Step3"
    )
)
  • ikibirev's avatar
    ikibirev
    Frequent Visitor
    7 years ago

    AlB, Thank you so much for your assistance! It's genius!