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,
How can we summarize the following data?
EmployeeNumber | Status | Date |
100 | 1 | 1/1/2017 |
100 | 2 | 2/1/2017 |
100 | 3 | 3/1/2017 |
100 | 4 | 4/1/2017 |
101 | 1 | 4/25/2017 |
101 | 2 | 5/25/2017 |
102 | 2 | 3/10/2017 |
103 | 2 | 4/10/2017 |
104 | 1 | 5/10/2017 |
105 | 1 | 6/10/2017 |
106 | 1 | 7/10/2017 |
106 | 2 | 8/10/2017 |
106 | 3 | 9/10/2017 |
106 | 4 | 10/10/2017 |
I want to display max(date) for the Employees. In this case the output will be:
EmployeeNumber | Status | Date |
100 | 4 | 4/1/2017 |
101 | 2 | 5/25/2017 |
102 | 2 | 3/10/2017 |
103 | 2 | 4/10/2017 |
104 | 1 | 5/10/2017 |
105 | 1 | 6/10/2017 |
106 | 4 | 10/10/2017 |
How can we do this? I tried to summarize and Group by but it's not working.
I want to create a summary table that does this. So that I can use my measures to on top that summary table. Is there a way to create a new calculated table in Power BI to achieve the above result?
Using matrix visual will work but in that case I cannot create my own measures to leverage that data. I appreciate your time. Thanks!
Hi @sunny27,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @sunny27,
The formula below will generate a calculated table. Please give it try. I assumed the orders of status and date are accordingly.
Table = SUMMARIZE ( 'Table1', Table1[EmployeeNumber], "maxStatus", MAX ( 'Table1'[Status] ), "maxDate", MAX ( 'Table1'[Date] ) )
Best Regards,
Dale
Hi,
Share the final result you are expecting. It could be possible that a formula can be written to get your result directly from the input Table.
Hi @sunny27
Create a measure in DAX:
Max in group = IF(CALCULATE(MAX('Table1'[Date]),ALLEXCEPT('Table1','Table1'[EmpNo]))=MAX('Table1'[Date]),1,0)
and then apply filter for this measure to 1 as visual filter.
This calculation doesnt load into model, so its memory / performance effective.
Thanks
Raj
Hi Rajendran,
Thanks for replying but here is the thing, Is there a way to create a measure or Calculated field to find the latest status of a facility by Date.I tried using [Max in group] measure and tried filtering in visual but no use.
Thanks!
Hi @sunny27,
I created 2 simple measures ( for date and Status) and I put them in my report (Table), it seems to works
M_Status = CALCULATE(MAX(Data[Status]))
M_Date = CALCULATE(MAX(Data[Date]))
Hope it helps...
Ninter
Goto Edit Queries
and Try this
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |