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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors