topic Re: Slicer does not respond to visual, probably because there is no direct relationship in DAX Commands and Tips

Slicer does not respond to visual, probably because there is no direct relationship

Anonymous — Thu, 27 Jul 2023 08:07:29 GMT

I have the following tables in my data model. (This is only a small part of a large data model, but these are the relevant tables. I have no influence on the shape of the model, I am just creating dashboards. )

Using the information in FCT Fuel, I calculated the average price per fuel session, filtering on electric cars.

avg_price_per_fuel_session = CALCULATE(

DIVIDE(SUM('FCT Fuel'[COSTPRICEEXCLVAT]), COUNTROWS('FCT Fuel')),

'DIM FuelType'[FUELGROUP] = "Electric"

)

This works as expected, and I can create visuals using this measure.

But would like to be able to use a slicer with 'DIM Leaseobject'[Make] in it, but the visual does not respond to that slicer.
I can understand that that is probably because there isn't a direct relationship between 'FCT Fuel' and 'DIM Leaseobject'.

I tried to add 'FCT Contract' in the measure. The visual now responds to the slicer, but the outcome of the calculation is off.

avg_price_per_fuel_session = CALCULATE(

DIVIDE(SUM('FCT Fuel'[COSTPRICEEXCLVAT]), COUNTROWS('FCT Fuel')),

'DIM FuelType'[FUELGROUP] = "Electric",

'FCT Contract'

)

Does anyone know how I can fix this issue?

Re: Slicer does not respond to visual, probably because there is no direct relationship

Syk — Thu, 27 Jul 2023 11:47:43 GMT

From the naming convention it looks like you have 2 fact tables? You won't be able to create your measure with the current data model. If you look at the little arrows between the relationships, that indicates which direction the data can be filtered. The only table that will be able to filter your FCT Fuel table will be DIM contracts.

Re: Slicer does not respond to visual, probably because there is no direct relationship

Anonymous — Thu, 27 Jul 2023 12:28:45 GMT

That is correct.

Do you see another solution that would get the desired outcome?

I'm thinking, I could try to add a calculated column with Leaseobject_PK to FCT Fuel, using somging like VLOOKUP. I could then create a realtionship between FCT Fuel and DIM Leaseobject.