Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Please help me to derive new calculated field(MainTask -Status) in Power BI by using DAX. I have status(SubTask- Status) field which is having data at sub task(SubTask) level, based on it i have create new column(MainTask -Status) at MainTask level.
For Example, I have data like below,
MainTask SubTask SubTask- Status
----------- ---------- ------------------
A 01 Completed
02 To Do
03 In -Progress
B 04 Completed
C 05 To Do
D 06 Completed
07 In -Progress
And I am expecting output results like below, That means if any of the SubTask is In-Progress & To - Do & Completed at MainTask level, then it should be in In- Progress, etc..
MainTask MainTask -Status
----------- -------------------
A In -Progress
B Completed
C To Do
D In -Progress
Any help is much appreciated.
Thanks,
Ganesh
Solved! Go to Solution.
@Anonymous
So if you want another table - go to the Modeling tab - click new table and type this...
Summary Table = SUMMARIZE ( 'Table', 'Table'[Main Task], "MainTask - Status", IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask-Status] ), VALUES ( 'Table'[SubTask-Status] ) ) = 1, IF ( SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed", "Completed", SELECTEDVALUE ( 'Table'[SubTask-Status] ) ), "In-Progress" ) )
If you don't want another table but a Measure instead which you can use in a Table Visual
MainTask-Status Measure = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask-Status] ), VALUES ( 'Table'[SubTask-Status] ) ) = 1, IF ( SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed", "Completed", SELECTEDVALUE ( 'Table'[SubTask-Status] ) ), "In-Progress" )
EDIT: Okay so lets also add a column in the original table
MainTask-Status Column = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask] ), VALUES ( 'Table'[SubTask-Status] ), ALLEXCEPT ( 'Table', 'Table'[Main Task] ) ) = 1, IF ( 'Table'[SubTask-Status] = "Completed", "Completed", 'Table'[SubTask-Status] ), "In Progress" )
Hope this helps!
@Anonymous
So if you want another table - go to the Modeling tab - click new table and type this...
Summary Table = SUMMARIZE ( 'Table', 'Table'[Main Task], "MainTask - Status", IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask-Status] ), VALUES ( 'Table'[SubTask-Status] ) ) = 1, IF ( SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed", "Completed", SELECTEDVALUE ( 'Table'[SubTask-Status] ) ), "In-Progress" ) )
If you don't want another table but a Measure instead which you can use in a Table Visual
MainTask-Status Measure = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask-Status] ), VALUES ( 'Table'[SubTask-Status] ) ) = 1, IF ( SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed", "Completed", SELECTEDVALUE ( 'Table'[SubTask-Status] ) ), "In-Progress" )
EDIT: Okay so lets also add a column in the original table
MainTask-Status Column = IF ( CALCULATE ( DISTINCTCOUNT ( 'Table'[SubTask] ), VALUES ( 'Table'[SubTask-Status] ), ALLEXCEPT ( 'Table', 'Table'[Main Task] ) ) = 1, IF ( 'Table'[SubTask-Status] = "Completed", "Completed", 'Table'[SubTask-Status] ), "In Progress" )
Hope this helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |