creating measure that picks the maximum value of another measure

[Dunner2020]

Hi there,

I am not going into detail of my problem as I know that I may not be able to explain the issue properly. So I will try to explain in a simple manner. 

I have a measure (let say m1) that calculates a value based on the half-hour data.  It means that in 24 hours time period, m1 calculates 48 values. Now I want to create another measure (let say m2) that picks the maximum value among those 48 values. I tried to use max function but it thrown an error because I was using m1 inside the max function. Could anyone help me in fixing the issue?

[Tahreem24]

@Dunner2020 ,

You can not directly use the measure with aggregate functions. Could you please share the DAX formula you have used for measure1? So on this basis we can create another one. Else convert your m1 measure into Calculated column then use the aggregate function.

[Dunner2020]

@Tahreem24 ,

Measure1 = 

var Time30Min = MAX('Table'[Epoch Half Hour Number])

Return

SUMX(FILTER(filter(ALL('Table'),'Table'[Epoch Half Hour Number] <= Time30Min && 'Table'[Epoch Half Hour Number] >= Time30Min-47),'Table'[ConsumerMinutes])/91577

Epoch Half Hour Number is the unique number assigned to half-hour time slot.  I am not sure about 'Else convert your m1 measure into Calculated column then use the aggregate function'. could you please explain this?

[v-kelly-msft]

Hi  @Dunner2020 ,

Try a measure as below:

Measure2=MAXX(VALUES('Table'[Epoch Half Hour Number]),[Measure1])

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Dunner2020]

Hi @v-kelly-msft ,

I want to pick the maximum value from next 48 epoch number. With the measure you wrote, it picked the maximum from all value.

[v-kelly-msft]

Hi @Dunner2020 ,

Could you pls provide some sample data with expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Dunner2020]

Hi @v-kelly-msft ,

Thanks for your prompt reply. Here is the snapshot of how measure2 should calculate:

Data can be downloaded from the link:

https://1drv.ms/u/s!AucycxZHFe9TjT9bGzbJeroo3UUW?e=z6hxY1

[v-kelly-msft]

Hi @Dunner2020 ,

Could you pls upload your .pbix file to onedrive business and share the link with us?

As tested here,the result from measure 1 is a bit different from yours.Pls add you measure 1 in the .pbix if updated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Dunner2020]

Hi @v-kelly-msft ,

Did you get the data? Do you have any update for me?

[v-kelly-msft]

Hi @Dunner2020 ,

Yes, I have got the data of an excel format,but based on my test,I have a different result from yours,so I advised you to add the measure in your data,or if possible,better upload your .pbix file to onedrive business and share the link with us.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!