topic Re: Checking if all the values are the same in DAX Commands and Tips

Checking if all the values are the same

Victormar — Mon, 14 Nov 2022 09:12:19 GMT

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 🙂

Re: Checking if all the values are the same

ValtteriN — Mon, 14 Nov 2022 09:23:13 GMT

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/

Re: Checking if all the values are the same

Victormar — Mon, 14 Nov 2022 11:05:30 GMT

That worked perfectly! Thanks 🙂

Re: Checking if all the values are the same

Victormar — Mon, 14 Nov 2022 11:21:08 GMT

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!

Re: Checking if all the values are the same

ValtteriN — Mon, 14 Nov 2022 12:42:33 GMT

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]))))

Re: Checking if all the values are the same

Victormar — Mon, 14 Nov 2022 14:42:37 GMT

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])

)