Grouping table by condition

[ikibirev]

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?

[AlB]

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]

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]

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 · Accepted solution]

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]

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