topic How to get most productive name with the value? in DAX Commands and Tips

How to get most productive name with the value?

Anonymous — Tue, 08 Feb 2022 12:58:46 GMT

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?

Most Productive Project =
VAR summarizedTable =
CALCULATETABLE (
SUMMARIZE (
'Employee Details',
'Employee Details'[Project],
"divides", DIVIDE ( [Total Productive Hours], [Total Hours Worked], 0 )
),
'Employee Details'[Project] <> "Free Time"
)
RETURN
MAXX ( summarizedTable, [divides] )

I got following result with this measure:

How do I fetch project name pertaining to this value?? Like Eg:- 0.95

Any help is appreciated. Thanks.

Re: How to get most productive name with the value?

Nathaniel_C — Tue, 30 Nov 2021 14:03:24 GMT

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

Re: How to get most productive name with the value?

Anonymous — Tue, 30 Nov 2021 16:31:16 GMT

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!

Re: How to get most productive name with the value?

Nathaniel_C — Tue, 30 Nov 2021 17:11:02 GMT

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])

Re: How to get most productive name with the value?

Anonymous — Tue, 30 Nov 2021 17:31:10 GMT

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.

Re: How to get most productive name with the value?

Nathaniel_C — Tue, 30 Nov 2021 18:30:57 GMT

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

Re: How to get most productive name with the value?

Nathaniel_C — Tue, 30 Nov 2021 19:08:55 GMT

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