Forum Discussion

phoisan's avatar
phoisan
Helper I
6 years ago
Solved

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

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

TimeInHoursNoOfFilterFilter costEnergy costChange and WasteTCO
10008.76963.6021.0243.801028.42
15005.84642.4031.5329.20703.13
20004.38481.8042.0421.90545.74
25003.50385.4452.5417.52455.50
30002.92321.2063.0514.60398.85
35002.50275.3173.5612.51361.39
40002.19240.9084.0710.95335.92
45001.95214.1394.589.73318.45
50001.75192.72105.098.76306.57
55001.59175.20115.607.96298.76
60001.46160.60126.117.30294.01
65001.35148.25136.616.74291.60
70001.25137.66147.126.26291.04
75001.17128.48157.635.84291.95
80001.10120.45168.145.48294.07
86701.01111.14182.225.05298.42
90000.97107.07189.164.87301.09
100000.8896.36210.184.38310.92
110000.8087.60231.193.98322.78
120000.7380.30252.213.65336.16
130000.6774.12273.233.37350.72
140000.6368.83294.253.13366.20
150000.5864.24315.262.92382.42
160000.5560.23336.282.74399.24
170000.5256.68357.302.58416.56
180000.4953.53378.322.43434.28
190000.4650.72399.332.31452.36
200000.4448.18420.352.19470.72
210000.4245.89441.372.09489.34
220000.4043.80462.391.99508.18
230000.3841.90483.411.90527.21
240000.3740.15504.421.83546.40
250000.3538.54525.441.75565.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

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

4 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.