cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)