Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
avendanof
Helper I
Helper I

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.