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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors