cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ikibirev Frequent Visitor
Frequent Visitor

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Grouping table by condition

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

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Grouping table by condition

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 Frequent Visitor
Frequent Visitor

Re: Grouping table by condition

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 Frequent Visitor
Frequent Visitor

Re: Grouping table by condition

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

 

t_e.png

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.

Super User
Super User

Re: Grouping table by condition

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

View solution in original post

ikibirev Frequent Visitor
Frequent Visitor

Re: Grouping table by condition

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,988)