Checking if all the values are the same

[Victormar]

Hello community,

I am working in a measure that shows the contracted speed value for a bus fleet. The key is that all the buses are supposed to have the same value, and this is what I would like to check with a measure.

My idea is to check if all the values from a selected fleet are the same and then return the value, but in case there are 2 or more different values, I would like to show a message like "More than two values available".

Hope it makes sense!

Thanks in advance 🙂

[Victormar]

oh wait, I thought so, my formula would look like:

Contracted AVG Energy Usage kWh/km = if(

    ISFILTERED(DIM_Chassis[FLEET_FLEET_ID]) && IF(COUNTROWS(DISTINCT(DIM_Chassis[FLEET_FLEET_ID]))>1,1,0),

    "More than one value",

    MAX(DIM_Chassis[D_ENERGY_CONSUMPTION_THEORICAL])

    )

But it's actually not working the way I want, I think is because I need only the buses in the selected fleet. I was thinking to try a variable? Something like:

Contracted AVG Energy Usage kWh/km =

var fleet = ISFILTERED(DIM_Chassis[FLEET_FLEET_ID])

return if (

    COUNTROWS(DISTINCT(DIM_Chassis[FLEET_FLEET_ID] && ...

    "More than one value",

    MAX(DIM_Chassis[D_ENERGY_CONSUMPTION_THEORICAL])

    )

Thanks again!

[ValtteriN]

Hmm,

How about using ALLSELECTED to limit the filter context to only selected fleet? So something like this structure: IF(COUNTROWS(DISTINCT(ALLSELECTED(DIM_Chassis[FLEET_FLEET_ID]))))

[Victormar]

mmm, yeah I see the logic, but then it turns out that it's returning how many fleets I have selected, not if the chassis inside the fleet have the same value, if that makes sense?

Contracted AVG Energy Usage kWh/km = if(

    ISFILTERED(DIM_Chassis[FLEET_FLEET_ID]) && IF(COUNTROWS(DISTINCT(ALLSELECTED(DIM_Chassis[T_CHASSIS])))>1,1,0),

    "More than one value",

    MAX(DIM_Chassis[D_ENERGY_CONSUMPTION_THEORICAL])

    )

[ValtteriN · Accepted solution]

Hi,

You could utilize COUNTROWS and DISTINCT.
DAX:

Measure 21 =

IF(COUNTROWS(DISTINCT('Table (12)'[Column2]))>1,1,0)

E.g. (non distinct values:

e.g. distinct:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Victormar]

That worked perfectly! Thanks 🙂