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

I need more complicated measure...

I can build one more table:
TempTable = filter('MainTable', 'MainTable'[Task] = 3)


But I couldn't connect these two tables and use Related, because unfortunately sometimes there are several same tasks for one ID and so link type is many-to-many.

 

And I want to solve it without TempTable, just DAX formula.

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
    Icon for Community Champion rankCommunity 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!