Skip to main content
cancel
Showing results for
Search instead 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

Helper IV

## Checking if all the values are the same

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 🙂

1 ACCEPTED SOLUTION
Super User

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/

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

Proud to be a Super User!

5 REPLIES 5
Helper IV

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!
Super User

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

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

Proud to be a Super User!

Helper IV

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

Super User

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/

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

Proud to be a Super User!

Helper IV

That worked perfectly! Thanks 🙂

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors