topic Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS in DAX Commands and Tips

Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Tue, 28 Apr 2020 13:10:11 GMT

Hello,

I have a relatively simple measure for calculating differences of volumes between months.

Measure = SUM(Volume) - CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod))

It works good in general but there are some issues.

I want to see only relevant months so I created a measure in my fact table and applied this measure as a filter on the visual where Rows are greater than or equal 1:

Rows = COUNTROWS(FactTable)

And finally, I have a slicer for year selection. When I choose one year or all it works good but when I choose more years from slicer I receive wrong value on the start of each year (not the delta but only the SUM(Volume). The reason is that CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod)) is null for the first month of each selected year. I don't know why. For the first selected year, it is understandable but why for the second selected as well?

Could someone help me, please?

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

Mariusz — Tue, 28 Apr 2020 16:03:10 GMT

Hi soldous

PREVIOUSMONTH function is a Time intelligence function so it requires Date Dimension or Or Auto Date/Time Hierarchies

Are you using the Date Dimension / Calendar Table to work correctly?
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Tue, 28 Apr 2020 16:13:02 GMT

Hello Mariusz,

Well, basically yes. I use a date dimension but not to the day granularity but only to month granularity. We basically have records for each first day of the month. I also set this table as Date Table.

The strangest thing for me is that when I don't filter the visual with COUNTROWS it works. But it also adds one more column to the end where there are no records for the current month but PREVIOUSMONTH gives me the last month. So I need the COUNTROWS filter and then it doesn't work.

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

Mariusz — Tue, 28 Apr 2020 16:25:13 GMT

Hi soldous

I imagine that PREVIOUSMONTH would return BLANK in the first month as there will be no data, have you investigated what this part of your measure return in these situations?

CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod))

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Tue, 28 Apr 2020 16:51:33 GMT

I added two pictures for better understanding.

On the first picture, you can see the situation with more selection in the slicer and bad result:

And on the second picture, you can see the situation when all months from slicer are selected. So basically the same situation but with the different and correct result:

Thanks a lot.

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

Anonymous — Tue, 28 Apr 2020 17:35:38 GMT

The answer is obvious if you know how DAX works. By putting a filter on Year, you are calculating your functions with this filter on. You do not take it off at all in your functions. PREVIOUSMONTH works the way you'd expect ONLY IF YOU HAVE A CORRECT DATE TABLE IN THE MODEL. With all dates in it and marked as the Date table. Please do yourself a favor. Create a normal, correct Date table, connect it to your fact, mark it as the Date table in the model and hide the Date column in it (as you don't need it). Then and only then, if you start using time-intel functions with the Date column, you'll get correct answers. Please always use time-intel functions with a PROPER CALENDAR. Do not cut corners.

Best
D

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Wed, 29 Apr 2020 08:36:30 GMT

Hi Anonymous

Thank you for your feedback.

I changed the date dimension to complete date dimension with date field for each date. The model is in the SSAS and the table is marked as Date Table with this whole date column as a unique identifier.

But the situation with the measure is the same.

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

sanimesa — Wed, 29 Apr 2020 08:56:22 GMT

You could create a previous month measure without using the previousmonth function. Simply get the month in the context and then subtract 1 from it. You can also test for if the year has been filtered using hasonevalue etc.

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Wed, 29 Apr 2020 09:04:38 GMT

Hello, sanimesa,

Thanks for your answer.

I've already tried DATEADD(MonthPeriod,-1,Month) or PARALLELPERIOD(MonthPeriod,-1,Month). ParallelPeriod gives me the same result as PreviousMonth. DateAdd gives me completely wrong results with many blanks.

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

Anonymous — Wed, 29 Apr 2020 09:38:42 GMT

DATEADD( MonthPeriod, -1, MONTH )? Is MonthPeriod a DATE? If it's not, then you know why it does not work. As I already said, time-intel functions need a date column (without gaps) to work correctly.

Best
D

Re: Different result for DAX measure when filtered out only relevant records with COUNTROWS

soldous — Wed, 29 Apr 2020 10:23:50 GMT

That's it! I didn't get it on the first time.

Thanks a lot for your help.

Best regards