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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors