Dax Formula to find min value and max based off other column

[DavidUK13]

Hello Power Bi Community,

I'm having some trouble with some Dax.

I have a table that has how much energy has been imported from the grid and if this occured in Peak or Off Peak times. I would like to find the min value and max if the occured within the Off Peak time window and then subtract one from the other.

Table1 Import from Grid contains values from 0.0 to 20.5 and the range in Off Peak Min is 0.2 and the Off Peak Max is 5.6.

So I would like to write something like:

If Peak/Off Peak = "Peak" then Min in Import from Grid = 0.2

If Peak/Off Peak = "Peak" then Max in Import from Grid = 5.6
Usage = 5.6-0.2 = 5.4

Any help is greatly appreciated.

[DavidUK13]

Hi, thank you for the help.

I think I'm approaching this in the wrong way. I need to make a plan on what exactly I want to achieve with the data. I have so many ideas that I'm looking at one thing and thinking about something else and looking that up and getting no where.

I think I need to go back to basics.

[DavidUK13]

Here we go:

https://drive.google.com/file/d/1by7gguR_vO-1ozjBir_Tdgljbff4wjge/view?usp=share_link

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  There is no peak entry in the Peak/off peak column that is why nothing shows up in the matrix.  As and when you add data, the result should show up.

Hope this helps.

[DavidUK13]

Is there anyway to upload the file so that the community can see rather than try to understand my poor explanation?

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  You may upload your file to Google Drive and share the download link here. 

[v-xinruzhu-msft]

Hi @DavidUK13 

Do you need to filter your maximum and minimum values based on the given date?

Based on your problem description, here is the code I provided to create three measures respectively, representing the maximum value of the off_peak, the minimum value, and their difference

Minx_OffPeak = MINX(FILTER(ALLSELECTED('Table'),'Table'[Peak/Off-peak]="Off Peak"),[Import frpm Grid Today])

Max_OffPeak = MAXX(FILTER(ALLSELECTED('Table'),'Table'[Peak/Off-peak]="Off Peak"),[Import frpm Grid Today])

Subtract = [Max_OffPeak]-[Minx_OffPeak]

You can also combine them into a single measure:

Subtract = var a=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Peak/Off-peak]="Off Peak"),[Import frpm Grid Today])

var b=MINX(FILTER(ALLSELECTED('Table'),'Table'[Peak/Off-peak]="Off Peak"),[Import frpm Grid Today])

return a-b

Best Regards,

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[DavidUK13]

Hi @v-xinruzhu-msft ,

While the solution provided by reddit did work. The measure works as long as only a single day is selected as a slicer or filter. If more than one day is selected either several days, a month or all entries then the highest value - the lowest value is calculated. I really need it by day so I can then sum that total for all time.

In your original message you asked if was for a given date, I would like it for each date so.

So it could be 5.6-0.2=5.4 for one day and 8.6-1.1=7.5 for the next day.