Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I wanted to create a measure which will return a specific column value based on other measure
TimeInHours is a column
TCO is a measure
I manage to find the min TCO value = 291.04. I want to get the TimeInHours value of min TCO = 7000
TimeInHours | NoOfFilter | Filter cost | Energy cost | Change and Waste | TCO |
1000 | 8.76 | 963.60 | 21.02 | 43.80 | 1028.42 |
1500 | 5.84 | 642.40 | 31.53 | 29.20 | 703.13 |
2000 | 4.38 | 481.80 | 42.04 | 21.90 | 545.74 |
2500 | 3.50 | 385.44 | 52.54 | 17.52 | 455.50 |
3000 | 2.92 | 321.20 | 63.05 | 14.60 | 398.85 |
3500 | 2.50 | 275.31 | 73.56 | 12.51 | 361.39 |
4000 | 2.19 | 240.90 | 84.07 | 10.95 | 335.92 |
4500 | 1.95 | 214.13 | 94.58 | 9.73 | 318.45 |
5000 | 1.75 | 192.72 | 105.09 | 8.76 | 306.57 |
5500 | 1.59 | 175.20 | 115.60 | 7.96 | 298.76 |
6000 | 1.46 | 160.60 | 126.11 | 7.30 | 294.01 |
6500 | 1.35 | 148.25 | 136.61 | 6.74 | 291.60 |
7000 | 1.25 | 137.66 | 147.12 | 6.26 | 291.04 |
7500 | 1.17 | 128.48 | 157.63 | 5.84 | 291.95 |
8000 | 1.10 | 120.45 | 168.14 | 5.48 | 294.07 |
8670 | 1.01 | 111.14 | 182.22 | 5.05 | 298.42 |
9000 | 0.97 | 107.07 | 189.16 | 4.87 | 301.09 |
10000 | 0.88 | 96.36 | 210.18 | 4.38 | 310.92 |
11000 | 0.80 | 87.60 | 231.19 | 3.98 | 322.78 |
12000 | 0.73 | 80.30 | 252.21 | 3.65 | 336.16 |
13000 | 0.67 | 74.12 | 273.23 | 3.37 | 350.72 |
14000 | 0.63 | 68.83 | 294.25 | 3.13 | 366.20 |
15000 | 0.58 | 64.24 | 315.26 | 2.92 | 382.42 |
16000 | 0.55 | 60.23 | 336.28 | 2.74 | 399.24 |
17000 | 0.52 | 56.68 | 357.30 | 2.58 | 416.56 |
18000 | 0.49 | 53.53 | 378.32 | 2.43 | 434.28 |
19000 | 0.46 | 50.72 | 399.33 | 2.31 | 452.36 |
20000 | 0.44 | 48.18 | 420.35 | 2.19 | 470.72 |
21000 | 0.42 | 45.89 | 441.37 | 2.09 | 489.34 |
22000 | 0.40 | 43.80 | 462.39 | 1.99 | 508.18 |
23000 | 0.38 | 41.90 | 483.41 | 1.90 | 527.21 |
24000 | 0.37 | 40.15 | 504.42 | 1.83 | 546.40 |
25000 | 0.35 | 38.54 | 525.44 | 1.75 | 565.74 |
Is there a way to create a measure such as this?
Here is the link to download pbix file
https://www.dropbox.com/s/jqootab2he0agui/Sample.pbix?dl=0
Thank you
Solved! Go to Solution.
Hi @phoisan ,
You may create measure like DAX below.
Measure 1 =
var _Table=SUMMARIZE(TimeFrame, TimeFrame[TimeInHours], "tcd" , [TCO])
Var MinTcd=MINX(_Table, [tcd])
return
CALCULATE(FIRSTNONBLANK(TimeFrame[TimeInHours], 1),FILTER(_Table, [tcd]=MinTcd))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @phoisan ,
You may create measure like DAX below.
Measure 1 =
var _Table=SUMMARIZE(TimeFrame, TimeFrame[TimeInHours], "tcd" , [TCO])
Var MinTcd=MINX(_Table, [tcd])
return
CALCULATE(FIRSTNONBLANK(TimeFrame[TimeInHours], 1),FILTER(_Table, [tcd]=MinTcd))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This measure works
CALCULATE(CONCATENATEX(VALUES(TimeFrame[TimeInHours]),TimeFrame[TimeInHours],","),FILTER(VALUES(TimeFrame[TimeInHours]),[TCO]=VAR __Table =
SUMMARIZE(
TimeFrame,
TimeFrame[TimeInHours],
"ABC",TimeFrame[TimeInHours],
"TCO",[TCO]
)
VAR _MinTCO = MINX(__Table,[TCO])
Return
_MinTCO ))
Hope this helps.
Hello @phoisan
You may try this:
Opt TimeInHours =
VAR _OptTCO = [Optimum TCO]
VAR _Table =
ADDCOLUMNS(TimeFrame,"TimeInHoursTest",TimeFrame[TimeInHours],"TCO",[TCO])
VAR _Filter =
CALCULATE(
VALUES(TimeFrame[TimeInHours]),
FILTER(_Table,[TCO] = _OptTCO)
)
RETURN
_Filter
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn