Forum Discussion

Victormar's avatar
Victormar
Icon for Helper V rankHelper V
3 years ago
Solved

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 🙂

DAX
measure
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    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/



     

5 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
    Victormar
    Icon for Helper V rankHelper V
    3 years ago

    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's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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's avatar
        Victormar
        Icon for Helper V rankHelper V
        3 years ago

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