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.
Another DX Query Im struggling with. I want to end up with a card. Heres some fake data
Ref Stage Person Hours
1 1 1 3
1 2 1 5
1 3 1 1
1 3 2 4
1 4 1 7
1 6 2 4
1 7 3 3
2 1 4 7
First of all i want to SUM Hours By Ref and Person. So Person 1 for Ref 1 woulkd be 15 hours
And the card would show the MAX Hours. So for this example Its person 1 with 15 Hours so this would show in the card
I dont really know where to start on this one. Here is the SQL Query that works on the data
SELECT MAX(Hours) FROM ( select Ref, Person SUM(Hours ) AS Hours FROM table1 GROUP BY Ref, Person ) a GROUP BY [Incident Ref]
The report is already filtered on a single Reference
Solved! Go to Solution.
@DebbieE try following two measures
first get sum by ref/person and then find the max hours
Sum = CALCULATE( SUM( Table3[hours] ), ALLEXCEPT( Table3, Table3[person], Table3[ref] ) ) MAX = MAXX( Table3, [Sum] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Nevermind the previous measure, it's incorrect 🙂
Here:
Measure = SUMX( TopN( 1; Summarize( 'Table'; 'Table'[Ref]; 'Table'[Person]; "Value" ; Sum('Table'[Hours]) ); [Value] ); [Value])
I just dont know what Im doing wrong.
On my actual data set I have 1 ref and the max hour is 4.4 hours
However this shows 17.60 hours. I cant even figure out where its getting that number from
Thank goodness I changed it to this and it works
Max Duration Hours lost = MAXX( TOPN(1,SUMMARIZE('fact OMSIncident','dim OMSIncident'[Incident Ref], 'dim OMSInterruptions'[Pro Number], "Value",SUM('fact OMSIncident'[Duration (Hours)]) ),[Value]), [Value])
Try this:
Measure = Calculate( Selectedvalue([Person]) & " with " & Selectedvalue([Value]); TopN( 1; Summarize( 'Table'; 'Table'[Ref]; 'Table'[Person]; "Value" ; Sum('Table'[Hours]) ); [Value] ))
Dont know if it will work 😄
The red values should be values created in the summarize statement.
Let me know if it works!
@DebbieE try following two measures
first get sum by ref/person and then find the max hours
Sum = CALCULATE( SUM( Table3[hours] ), ALLEXCEPT( Table3, Table3[person], Table3[ref] ) ) MAX = MAXX( Table3, [Sum] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Nevermind the previous measure, it's incorrect 🙂
Here:
Measure = SUMX( TopN( 1; Summarize( 'Table'; 'Table'[Ref]; 'Table'[Person]; "Value" ; Sum('Table'[Hours]) ); [Value] ); [Value])
I just dont know what Im doing wrong.
On my actual data set I have 1 ref and the max hour is 4.4 hours
However this shows 17.60 hours. I cant even figure out where its getting that number from
Thank goodness I changed it to this and it works
Max Duration Hours lost = MAXX( TOPN(1,SUMMARIZE('fact OMSIncident','dim OMSIncident'[Incident Ref], 'dim OMSInterruptions'[Pro Number], "Value",SUM('fact OMSIncident'[Duration (Hours)]) ),[Value]), [Value])
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |