topic Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set in DAX Commands and Tips

DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 15:23:52 GMT

I am doing some training work in AdventureWorksDW database.

I have two measures:

Sum of SalesAmount = SUM(FactInternetSales[SalesAmount])

MonthlyAverageSales = AVERAGEX (
VALUES(DimDate[EnglishMonthName]),
[Sum of SalesAmount]
)

I have developed the above report, choosing only two years 2006 and 2007, and only 4 months using slicers.

While the measure [Sum of SalesAmount] shows the correct value, the other measure [MonthlyAverageSales] shows incorrect data in the Total row; however, both measures are right, when aggregated for each year 2006 and 2007.

The measure [MonthlyAverageSales] at the Total row should actually be $ 5,916,696.73/8, which is $ 739,587.09. (8 in denominator represents 8 months - Jan,Feb,Nov,Dec for each year - 2006 and 2007)

Instead, what I see is $ 5,916,696.73/4, which is $1,479,174.18; (4 in denominator represents 4 months- Jan,Feb,Nov,Dec, combining both years together)

Now, I have changed the formula, and have created a new measure:

MonthlyAverageCorrect = AVERAGEX (
KEEPFILTERS(VALUES(DimDate[EnglishMonthName])),
[Sum of SalesAmount]
)

I still do not see any change. Instead of getting $ 739,587.09, I still see $1,479,174.18.

Where am I in error ?

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

MattAllington — Sun, 05 Jul 2020 01:26:41 GMT

Your Averagex iterates over the 4 months, not the 8 unique combinations of year and month. If you want to iterate over the instances of the months, you need a measure that will do that. You could try replacing values(cal[month name]) with summarize(sales,cal[month name],cal[year])

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

AntrikshSharma — Sun, 05 Jul 2020 08:21:25 GMT

Create a new column in date table that has month and year in same column example: January 2020 and then inside the measure use this column.

MonthlyAverageCorrect = AVERAGEX ( VALUES ( DimDate[Year Month] ), [Sum of SalesAmount] )

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 14:00:39 GMT

Thanks for the answers.

My goal is to understand KEEPFILTER function.

Can we solve this using KEEPFILTER ?

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

AntrikshSharma — Sun, 05 Jul 2020 14:03:32 GMT

Can you post the pbix file? Because the measure with KEEPFILTERS is correct.

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 14:32:30 GMT

I am not able to post the file, since I am a new member.

But all the measures and the data model relationships are shown as pictures.

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

AntrikshSharma — Sun, 05 Jul 2020 14:36:58 GMT

Upload the file to dropbox, google drive and share the link for the file.

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 14:56:22 GMT

https://drive.google.com/file/d/1mXeWjF0dw33KL4Vg-q3h8l3jGqB2oVJX/view?usp=sharing

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

AntrikshSharma — Sun, 05 Jul 2020 15:25:13 GMT

You could try this:

Avg = AVERAGEX ( KEEPFILTERS ( FILTER ( ALL ( DimDate[EnglishMonthName], DimDate[CalendarYear] ), [Sum of SalesAmount] > 0 ) ), [Sum of SalesAmount] )

Avg CROSSFILTER = AVERAGEX ( CROSSJOIN ( DISTINCT ( DimDate[EnglishMonthName] ), DISTINCT ( DimDate[CalendarYear] ) ), [Sum of SalesAmount] )

Avg SUMMARIZE= AVERAGEX ( SUMMARIZE ( DimDate, DimDate[EnglishMonthName], DimDate[CalendarYear] ), [Sum of SalesAmount] )

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

Anonymous — Sun, 05 Jul 2020 16:29:16 GMT

Hi snph1777 ,

Not sure if this is monthly average.

I think you just need the total divide by the no. of months.

You can use this measure.

Avg SUMMARIZE = var a = DISTINCTCOUNT(DimDate[YearMonthValue]) RETURN [Sum of SalesAmount]/a

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 16:36:24 GMT

Ok thanks; any reason, why my formula did not work ? I have imported an Excel Power Pivot workbook into Power BI

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 17:15:46 GMT

Thanks

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

snph1777 — Sun, 05 Jul 2020 17:16:34 GMT

Thanks

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

AntrikshSharma — Sun, 05 Jul 2020 17:20:16 GMT

Because when context transition happens it creates a filter for each month and then overwrites existing filter over months but the years remain untouched and let's say the first value from VALUES () is January due to context transition CALCULATE overwrites filters over months and the new filter is with January, January is calculated both for 2007 & 2008, so either you use

Average Sales = AVERAGEX( VALUES( 'Date'[Calendar Year Month]) , [Total Sales] )

or the options above, creating a column in Date table is more efficient than KEEPFILTERS