The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have made a table visual with following fields and measure.
1. Project (Field value)
2. Total Hours Worked = sum(Hours)
3. Total Productive Hours = sum(Productive Hours)
I want to get the name of the project which has the highest value of (Total Productive Hours/Total Hours) along with that value in card visual.
Eg (Expected Output) : 0.95
I am able to get the highest value of (Total Productive Hours/Total Hours) with the following measure but how to fetch project name pertaining to that value?
How do I fetch project name pertaining to this value?? Like Eg:- 0.95
Any help is appreciated. Thanks.
Solved! Go to Solution.
Hi @Anonymous
Most productive name =
VAR _Percent =
maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours])) //This figures the max value
VAR _name =
CALCULATE (
MAX ( Productive[Project] ),
FILTER ( ALL ( Productive ),DIVIDE(Productive[Productive Hours],Productive[Hours])= _Percent)
)
RETURN
_name
Try this, this figures the max value, outside of the calculate, and then goes through the table row by row in the calculate function
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
Run your measure twice, once for the value and once for the name. Then create a measure that combines the name and the value like Access: 95. This will be the result of your measure which will go into your card, and you will turn off the category name. I use this all the time to mark an update time in a report. "Report last updated: May 5"
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C I had tried creating a measure for project name :
Most Productive Project Name = calculate(max('Employee Details'[Project]), 'Employee Details'[Project]=Most Productive Project
where 'Most Productive Project' is the max value as mentioned in my question.
But this method didn't work!
@Anonymous , Running out the door to an appointment, but try these measures. I created the percentage column in Power Query.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Most productive percentage =
VAR _Percent =
MAX ( Productive[Percentage1] )
return _Percent
============================================
Most productive name =
VAR _Percent =
MAX ( Productive[Percentage1] )
VAR _name =
CALCULATE (
MAX ( Productive[Project] ),
FILTER ( ALL ( Productive ), Productive[Percentage1] = _Percent )
)
RETURN
_name
=============================================================
Combine 2 measures = Concatenate(CONCATENATE([Most productive name]," is the most productive project " ),[Most productive percentage])
Proud to be a Super User!
Thanks for the reply.
But you have added percentage as a seperate column in the table. I am supposed to achieve the result without the need to add any extra column.
Hi @Anonymous , use MAXX instead of MAX
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Most productive name =
VAR _Percent =
maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours]))
VAR _name =
CALCULATE (
MAX ( Productive[Project] ),
FILTER ( ALL ( Productive ), Productive[Percentage1] = _Percent )
)
RETURN
_name
===============================
Most productive percentage =
VAR _Percent =
maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours]))
return _Percent
Proud to be a Super User!
Hi @Anonymous
Most productive name =
VAR _Percent =
maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours])) //This figures the max value
VAR _name =
CALCULATE (
MAX ( Productive[Project] ),
FILTER ( ALL ( Productive ),DIVIDE(Productive[Productive Hours],Productive[Hours])= _Percent)
)
RETURN
_name
Try this, this figures the max value, outside of the calculate, and then goes through the table row by row in the calculate function
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
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 |
---|---|
63 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
115 | |
33 | |
30 | |
24 | |
21 |