Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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" ) )
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" )
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.
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.
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" ) )
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |