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             02.01.2018
1              3             03.01.2018
2              1             01.02.2018
2              2             02.02.2018
3              3             01.03.2018
4              1             01.04.2018
4              2             02.04.2018
4             3              03.04.2018
4             4             04.04.2018

For example Task 3 is a key identifier. I want to get mesaure which allow to split ID by this key like this:

ID            Task3Exist
1             OK
2             KO
3             OK
4             OK

How to solve this?

 

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

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi ikibirev,

    Let's see if I've understood what you need. Try this, where Table1 is the first table you show:

    1. Place Table1[ID] in the rows of a matrix visual

    2. Create this measure and palce it in values of the matrix:

     

    Task3Exists =
IF ( COUNT ( Table1[Task] ) > 0, "OK", "KO" )

     

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