ALL Function doesn't work

Jkh30 — Mon, 24 Jul 2023 08:55:14 GMT

Hi,

I have a table like this:

Date	Value	Year	Month	Month No
01-Jan-22	10	2022	January	1
01-Jan-23	20	2023	January	1
01-Feb-22	30	2022	February	2
01-Feb-23	10	2023	February	2

And my requirement is to show all the year of the data from date range selection, for example if I choose the date between 01-01-2022 until 28-02-2022. The chart should show two lines, which the January and February of 2022 and 2023. I tried to use ALL to exclude the date but it's still filtering like this:

Measure = CALCULATE(SUM(Sheet1[Value]),ALL(Sheet1[Date]))

The visual is not showing the data of year 2023. The expected result is to show all the years.

Any help would be so appreciated. Thank you!

Re: ALL Function doesn't work

v-yueyunzh-msft — Wed, 26 Jul 2023 03:01:55 GMT

Hi , Jkh30

According to your description, you want just filter the Months in your slicer and show all the year.

For your need , here are the steps you can refer to :
(1)This is my test data:

(2)For your need , we can not use the [Date] in the fact table as the slicer , we need to create a dimension date table like this:(this is a calculated table ,we can click "New Table" and enter this to create )

Date = ADDCOLUMNS( CALENDAR(DATE(2021,1,1) ,DATE(2023,12,31)) ,"Year" , YEAR([Date]),"Month", FORMAT([Date],"mmmm") , "Month No" , MONTH([Date]) )

(3)Then we can create a measure like this:

Measure = var _slicer= VALUES('Date'[Month No]) var _year = MAX('Table'[Year]) var _month = MAX('Table'[Month No]) return IF( _month in _slicer , SUM('Table'[Value]),BLANK())

(4)Then we can put the 'Date'[Date] as the slicer and put the fields on the visual and then we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

topic Re: ALL Function doesn't work in DAX Commands and Tips

ALL Function doesn't work

Re: ALL Function doesn't work