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
gunicotra
Helper II
Helper II

ALL with slicer on yearly campaign data

Hi to all,

I have "yearly campaign" values and a slicer with which I can select the yearly capmaign.

Then I have a measure that should bring the count of values of the previus year:


FA_PY =
var YearPrec=SELECTEDVALUE(Table[Year])-1
return

calculate(COUNTA(Table[ID]),
ALL(Table[Year]),
Table[Year]=YearPrec)

However the result is null becouse if I select on slicer the year 2021, the measure search for 2020 data but I guess this dis-allignment of years (slicer 2021, and measure 2020) determines the fact that the measure is "null" even if I have built it with ALL(Table[Year]), and then re-define it with the new year Table[Year]=YearPrec).

How can I do this?
ps.: I can not use the time intelligent formulas becouse the data are releted to a "yearly campaign" which might also be broader that a year itself!
thanks to all
G
1 ACCEPTED SOLUTION

Hi, @gunicotra 

 

You can modify the code like this to test where the problem is:

FA_PY =
calculate(COUNT(Table[ID]),
ALL(Table[Year]),
Table[Year]=SELECTEDVALUE(Table[Year]))

I can't find the problem from your description. So I sugget you share some sample data with us to check.

If the problem still exists, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

There is no need to use ALL() when you then specify a replacement filter value.  Your scenario should work as you describe. Maybe you can provide some sample data to illustrate the issue.  

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

sure.
I have this year slicer filter: 

gunicotra_0-1636737538293.png
and a measure:

FA_PY =
var YearPrec=SELECTEDVALUE(Table[Year])-1
return
calculate(COUNTA(Table[ID]),
ALL(Table[Year]),
Table[Year]=YearPrec)

 

According to this data, I would expect that, though I have indicated 2020 in the filter slicer, the measure would give me the 2019 data. The result is, instead:

- "blank" if you consider the measure as it is represented; 

- "all the correct data related to the 2020 campaignn year", if you replace the "YearPrec" var with 2020..

 

As far as I can see the ALL() does not work properly. 

I have also removed the ALL() statement leaving just the "Table[Year]=YearPrec", as you suggested, but the result has been the same.

Hi, @gunicotra 

 

You can modify the code like this to test where the problem is:

FA_PY =
calculate(COUNT(Table[ID]),
ALL(Table[Year]),
Table[Year]=SELECTEDVALUE(Table[Year]))

I can't find the problem from your description. So I sugget you share some sample data with us to check.

If the problem still exists, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

I have followed your suggestion and I found that my slicer was erroeously applied to a Table1[Year], while the measure was defined on a different table Table[Year]!
Though the two table were connected through this same columns, the desired result did not happend.
Thanks for you hint 😉

Hi, @gunicotra  

 

So, After you modify, is everything normal?

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If you still need help, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

yes thanks!

Hi,

You should have a Calendar Table with Year as a calculated column in the Calendar Table.  To your slicer, drag Year from the Calendar Table.  Write this measure

FA_PY = calculate(COUNTA(Table[ID]),previousyear(calendar[date]))
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish_Mathur,

thanks for your suggegstion. Unfortunately I can not use time intelligent functions becouse the yearly campaign data are not to be fond in the same yearly period of the previus yesr (as an example: I can have 2020 campaing data from november 19 to septemper 20 and the 2021 capmaing data from november 20 to december 22). Using time intelligent function, I may loose some data

Maybe you can provide some sample data to illustrate the issue.  

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.