cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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)

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:

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
Solution Sage
I think it may be implying a filter context.
5 REPLIES 5
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.

Helper I

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.

Solution Sage

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)

Helper I

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

Solution Sage
I think it may be implying a filter context.