Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello All,
I am working in composite model in power bi. Used summarize function to create a import table from direct query table .
How will i group emp id and month to get the hours in one single line item insated of 4.
Note: Group by in Power query is not possible as this is a sumarrised table from direct query
Thank you
KIndly advise
Solved! Go to Solution.
Hi @unknown_anony ,
Thanks for @Sergii24 reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create a new table
Table 2 =
SUMMARIZE(
'Table',
'Table'[Emp.ID],
'Table'[SAP Month],
"SUM", CALCULATE(
SUM('Table'[Record hours]),
ALLEXCEPT(
'Table',
'Table'[Emp.ID],
'Table'[SAP Month]
)
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @unknown_anony ,
Thanks for @Sergii24 reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create a new table
Table 2 =
SUMMARIZE(
'Table',
'Table'[Emp.ID],
'Table'[SAP Month],
"SUM", CALCULATE(
SUM('Table'[Record hours]),
ALLEXCEPT(
'Table',
'Table'[Emp.ID],
'Table'[SAP Month]
)
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @unknown_anony , could you share with us your DAX code for creating this table? Summrize function should group the table by columns you specify and then perform sum operation over Recorded Hours.
The result from you screehshot seems to be achieved by a following expression:
SUMMARIZE(
_DirectQueryTable,
[Emp.ID],
[SAP Month],
[Recorder Hours]
)
If it's so, your result is correct as PowerBI giving you unique combination of 3 of them for each row.
In order to sum Recorder Hours you need to specify it in Name and Expression parameters of Summarize()
So your code should be similar to this:
SUMMARIZE(
_DirectQueryTable,
[Emp.ID],
[SAP Month],
"@RecordedHours", SUM( _DirectQueryTable[Recorded Hours] )
)
I hope it helps! 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |