cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avendanof
Frequent Visitor

DAX formula with month name instead of month number: Is this behavior correct

Hi, 

 

I have two formulas:

 

Total sales all months alfa = calculate([Total de Ventas]; all(DimFecha[SpanishMonthName]))

 

And:

 

Total sales all months numeric = calculate([Total de Ventas]; all(DimFecha[MonthNumberOfYear]))

 

If the field DimFecha[SpanishMonthName] is sorted by the field [MonthNumberofYear]

 

I get this (see the second column)

 

img 1.png

 

You may see that the calculation is done by each month and is not taking care of the all() filter I´m using in the calculate.

 

And if it´s not sorted by the field [MonthNumberofYear] I get this, which is what I think is correct:

 

 

img 2.png

Here is taking care of the all() filter.

 

And you also may see that the field based on the field  [MonthNumberofYear] remains the same too in any of two scenarios.

 

Is this behavior correct? Why the change in the order changes the behavior of the field affecting the all() in the calculate.

 

Thanks for an answer.!!!

 

 

1 ACCEPTED SOLUTION

I think it may be implying a filter context.

View solution in original post

5 REPLIES 5
Seward12533
Solution Sage
Solution Sage

I would have to see your source data and model defintions but I'm assuming that your using the Year and month name from your FACT table in your visual and not the one from your DATE table or you have defined a BI-DIRECTIONAL cross filter relationship between the DATE table and FACT table. 

 

I think what is happending is when you define the sort column you are forcing filter context that is not applied when the columns are not related. 

 

If your relationship is set to crossfiltered BOTH try making it single and be sure you using the fields from your DATE table to build your visuals with. 

 

If this is not it please post a PBIX file with some sample data or at least share a pic of what your model looks like and the structure of our data tables and some representtaive data. 

 

 

I´m using the AdventureWorks model and the date source is not from the fact table but from the Dimdate table as it has to be. The direction of the relationship is also correct. 

I´m sharing the pbix for yoy to see.

in here:

https://lhcampus-my.sharepoint.com/:f:/g/personal/felix_avendano_lhcampus_onmicrosoft_com/EhW-qZUM4z...

 

Let me know if you have any trouble on accesing the folder.

 

Thanks for your support.

Try This - all works great with filteres set by slicers on lookup tables but the filter context implied by the ROW within your visual is still being implied (not sure whey that changes when you change the sort order I'll leave that to the real DAX experts.  I applied a standard dax pattern I frequently use for time based filtering including running totals etc..  In this case you need the ALL(Dimdate) to remove any filtering from the date table then expclitly define your own.

 

Total sales all months alfa = CALCULATE([Total de Ventas],ALL(DimDate),filter(ALL(DimDate[CalendarYear]),[CalendarYear]=DimDate[CalendarYear]))

 

For an explaination of this very flexible DAX pattern check out these blog posts form the P3 team on what they call the TGFITW (The Greatest Formula In The World) - note in your case you need the ALL since your visual is filtering on mutiple fileds from the date table (year and month)

 

part one,  part two , Part three, Part 4?

Yes, I know the TGFITW but what I has trying to understand is why is this odd behavior when you change the sort order.

 

I think it may be implying a filter context.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors