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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors