Measure returning multiple values based on slicer selection

[Victormar]

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! 🙂

[Victormar]

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 · Accepted solution]

@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. 

[Victormar]

Thanks for the explanation! That helped 😄

[Caz_16]

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. 

Truck	Exp. Date
Truck 1	1/1/22
Truck 2	4/1/22
Truck 3	7/1/22

[Victormar]

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]

@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