Create a measure which will return a specific column value based on other measure Power BI Desktop

[phoisan]

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

[v-xicai · Accepted 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.

[phoisan]

Thanks @v-xicai . The solution is working

[Ashish_Mathur]

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.

[vivran22]

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