cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## find max and min value from same column obtained from dynamic slicer selection and find difference

Dear all,

I need to create a dax for dynamically selecting sites from the slicer and find the difference from max & min selected.

For example, in above screenshot, I have selected 3 sites, out of which max is 316.46 and min is 31.55. so I need a measure

to return the difference and calculate the performance %

dax measure diff = 316.46 - 31.55

performance measure = dax measure diff/max(dax measure diff)

Could you please guide me on this?

Regards,

SC

2 ACCEPTED SOLUTIONS
Super User

Here is a sample of the pseudo code:

performance measure = divide(maxx(table,units)-minx(table,units),maxx(allselected(table),units)-minx(allselected(table),units))

you can refine that with SUMMARIZE(ALLSELECTED())  etc.  Really depends on how you want to interpret it.

Super User
``````performance measure =
var mmax = CALCULATE(max(DataProfile[Units]),allselected(Contacts[Name]))
var mmin = CALCULATE(min(DataProfile[Units]),allselected(Contacts[Name]))
return mmax-mmin``````
14 REPLIES 14
Super User

Here is a sample of the pseudo code:

performance measure = divide(maxx(table,units)-minx(table,units),maxx(allselected(table),units)-minx(allselected(table),units))

you can refine that with SUMMARIZE(ALLSELECTED())  etc.  Really depends on how you want to interpret it.

Post Patron

Hi,

Thank you so much for the response and this works brilliantly for the Data profile. But now the issue I have is with the below graph  viz 'Half-Hourly Profile'. This measure is not showing the performance at half hourly level? FYR, please see the below screenshot.

Super User

what is the expected outcome based on the data you provided?

Post Patron

Hi,

My sincere apologise for not giving the expected outcome. so if we see the below screenshot

This shows the units at half hourly level. Now according to the tooltip shown above performance measure gives value '0.00'. but the expected performance for corresponding date(in this case 08/20/2022) and time(in this case 12:00:00 AM) should be 3.95-2.99 =0.96 .

So 0.96 is the required performance at 12:00:00 AM and this should change according to the half hourly time. for example at 12:30:00 it should be (3.86-3.56=0.3 ). The date used for this visual (x-axis) is merged date from data profile table. Also, this visual should change dynamically when we move the date slicer(Date field from calendar table) as shown below

FYR, i have again shared the updated data file in below location

https://onedrive.live.com/?id=EA584763CE840515%21737&cid=EA584763CE840515

Please feel free to let me know if you need further info

Super User

Please check the OneDrive link - it comes up empty for me.

Post Patron

Hi,

Apologise, PFA link for the file

Profile Explorer (1) - Copy (1).pbix

Can you please confirm, if you are able to access this?

Please free to let me know if you are unable to access

Thanks

Super User
``````performance measure =
var mmax = CALCULATE(max(DataProfile[Units]),allselected(Contacts[Name]))
var mmin = CALCULATE(min(DataProfile[Units]),allselected(Contacts[Name]))
return mmax-mmin``````
Post Patron

Hi,

Many thanks & I will also accept this as a solution so that it will help others.

Today i was handling a data and had a query.

I am trying to create a dax in my report which i am unable to do. This is a different one so i have raised this as a new query. Can you please guide me through that if you don't mind. The link to that is

find the dates in a column that is not present in ... - Microsoft Power BI Community

Post Patron

Hi,

Many thanks for this beautiful solution and you cracked it.

This works like a charm. Many thanks for patiently guiding me on this and have noted this for future reference.

Once again thanks a lot sir

Kind regards,

Siva

Post Patron

Hi,

Apologise & PFA link to sample file location

I want to give additional info on the same:

The Relationship is and the fields used in dax measure are below:

My requirement is, I want to find max & min and show the difference in tooltip. for example, when i select 2 sites from slicer as shown in below screenshot

it should return 263.31-215.11= 48.2 in site variance tooltip but currently it shows zero.

but what i observed is when i turn this into table visual instead of line chart,it shows the difference in total level as below

Can you please guide me on this issue? How can show this difference total

in line chart tooltip?

Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

Post Patron
Super User

Post Patron

Hi,

Apologise, i am sharing my one drive, can you please try this link

Profile Explorer (1) - Copy.pbix

if not, please let me know your email id or a link to a location where i can save this

Thanks