Forum Discussion

Victormar's avatar
Victormar
Helper V
3 years ago
Solved

Measure returning multiple values based on slicer selection

Hello community!

I am working on a measure which I would like to return the different values for "expiring contract date" based on a slicer selection for the vehicles in every fleet.

Ideally, the user would select a fleet and then the different expiring dates for each vehicle would show.

 

I have been working with: 

Expiry Date = if(
    ISFILTERED(DIM_Chassis[FLEET]),
    VALUES(DIM_Chassis[CONTRACT_EXPIRING_DATE]),
    blank()
    )
But doesn't work when there are different expiring dates
I think it's not possible, but still want to try.
 
Thanks! 🙂
Need Help
  • Caz_16's avatar
    Caz_16
    3 years ago

    Victormar 

    I see. 

     

     

     

    Does this look ok? In a Matrix visual, I put the "Fleet" at the Primary Row and the "Expiration Date" as the Sub Row. Ive attached my .pbix file at the OneDrive link. Link to OneDrive 

     

    I guessed at how your data is formatted. You may be able to put your data into a summary table to get it to behave like this. 

     

6 Replies

  • Caz_16's avatar
    Caz_16
    Helper II
    3 years ago

     

    Expiriation Date =
IF(
    HASONEVALUE(
        [Fleet]
    ),
    [Expiration Date],
    BlANK()
)

     

     

     

    Expiriation Date =
IF(
    COUNTROWS(
        [Fleet]
    ) = 1,
    [Expiration Date],
    BlANK()
)

     

    Have you tried HASONEVALUE([Fleet]) or COUNTROWS([Fleet]) = 1? If you put this in the conditional parameter of IF(), then the measure should evaluate when the conditional is TRUE. 

     

    Also keep in mind that Measures are designed to return a single value. How you display the measure can alter how the measure behaves. If you want to display this in a table like below, then the above measure should work. 

    TruckExp. Date

    Truck 1

    		1/1/22
    Truck 24/1/22
    Truck 37/1/22

     

    • Victormar's avatar
      Victormar
      Helper V
      3 years ago

      Hello, and thanks for the reply 🙂

      I have tried but I get this error:

      Maybe the problem is that I am using the measures in a matrix as values, so I only show the result of the measures?

       

      Not sure from the formulas you shared though, after HASONEVALUE or COUNTROWS, Expiring_Date has to be a measure right? Cannot pass a column there (expiring_date is a column in my dataset).

       

      Hope it makes sense 😄

      • Caz_16's avatar
        Caz_16
        Helper II
        3 years ago

        Victormar 

        First https://gprivate.com/61eoy 

        Second, for the second parameter of your IF() statement should be a measure that returns the Expiration date of each truck. You can use an aggregate function in your measure such as MIN, MAX, FIRSTNONBLANK, to get the appropriate Expiration date for each truck. The other route to go would be to create a calculated column in the appropriate table. If you go with this method, then IF() can reference a column and return a value. 

         

        When creating measures, a great way to troubleshoot what it is doing is to put the measure in a table and add columns to see how the measure is being evaluated in row context. 

         

        Hope this helps. 

        Caz

         

  • Victormar's avatar
    Victormar
    Helper V
    3 years ago

    Hello,

    You didn't understand me, I don't want to add the trucks as a row in the matrix, that is my problem to show the different expiring dates.

    Ideally I would like to return the different expiring dates from the measure without having to add the trucks, and I am not sure it is possible, something like that:

    I am only adding the measures as values in the matrix.

    • Caz_16's avatar
      Caz_16
      Helper II
      3 years ago

      Victormar 

      I see. 

       

       

       

      Does this look ok? In a Matrix visual, I put the "Fleet" at the Primary Row and the "Expiration Date" as the Sub Row. Ive attached my .pbix file at the OneDrive link. Link to OneDrive 

       

      I guessed at how your data is formatted. You may be able to put your data into a summary table to get it to behave like this.