topic Re: Measure does not work with slicer. in DAX Commands and Tips

Measure does not work with slicer.

MrAndrewth85 — Fri, 03 Feb 2023 17:56:41 GMT

Hi.

I have the following formulas to calculate the variation in the number of customers from one year to another:

Num Customers Last Year = CALCULATE ( SUM ( FACT_Customers[Number of Customers]), PARALLELPERIOD ( DIM_Dates[Date], -12, MONTH) )

YTY Customer Growth = SUM (

FACT_Customers[Number of Customers]

)

- [Num Customers Last Year]

% YTY Member Growth = DIVIDE (
[YTY Customer Growth],[Num Customers Last Year]
)

When I use the card visual to see the % variation of customers, these formulas works perfectly if I filter on the visual an specific date. For example, (using the advanced filtering) "date is" 12/31/2022, and I get the percentage growth of customers with respect to 12/31/2021. But, if I add a slicer to freely choose any date, I get blank on the card. I already tried to change the filter to "is on or after" in order to have a wide range of dates, but it did not work. Also, I eliminated the filter, but this did not work either. Any idea how I can synchronize these formulas with the slicer?

Thanks in advance!

Andres

Re: Measure does not work with slicer.

amitchandak — Sat, 04 Feb 2023 02:29:06 GMT

MrAndrewth85 , Make sure dim_date is used in slicer and visual as per need. Join with Dim_date should be single directional and it should have all dates and marked as date table

Try LY like

Num Customers Last Year = CALCULATE ( SUM ( FACT_Customers[Number of Customers]), dateadd( DIM_Dates[Date], -12, MONTH) )

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Re: Measure does not work with slicer.

MrAndrewth85 — Mon, 06 Feb 2023 14:15:58 GMT

Thanks for your answer, amitchandak!

At the moment, the solution you have given me has not worked. In the model, DIM_Date and FACT_Customers tables are linked by Date Key, so I think it should be enough for the slicer to affect the measurements, but it is not doing it, and neither did it with the calculation that you suggested me to do for customers LY. Let me understand a little more about being sure dim_date is used in slicer and visual as per need. Do you mean that I use it in the filter of each visualization?

Thanks again for your answer!