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

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