Forum Discussion
ALL Function Not Working
Hello,
I am trying to create a a measure to use in a graph that is not impacted by a slicer filter called 'listing number' on the report. The measure I created is as follows:
25th Percentile Number Bookings = CALCULATE(CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER('Metrics by Listing','Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0)),ALL('Metrics by Listing'[Listing Number]))
I used the measure in a line graph where the month is on the x-axis and the number of bookings on the y-axis. However, when I change the listing number slicer, the graph changes.
5 Replies
Hi afelber,
Please modify the DAX like below:
25th Percentile Number Bookings = CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER(ALL('Metrics by Listing'),'Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0))
Best Regards,
Qiuyun YyuI do not want all filters removed though, just for the column 'listing number' in the table?
Hi afelber,
Please don't use ALL() function,
25th Percentile Number Bookings = CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER('Metrics by Listing','Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0))
Then select the slicer visual, enable Edit Interactions, click the NONE for chart. See: Visualization interactions in a Power BI report.
Best Regards,
Qiuyun Yu
Did you solve this issue? I have a similar problem where I have a Customer slicer on the page but want to get a percentage value based on the total Value regardless of the customer. The All function does not seem to work in this case. It filters the values by the customer selected in the slicer.
The query I am using to calculate the denominator is as follows:
Spend YTD = CALCULATE(Sum('Invoices'[Revenue]), ALL(Invoice_Customers[Customer Name]), Filter(Invoices, [IsYTD]))
The IsYTD measure in Invoices table returns true or false. I am using this to filter out all invoices that are not in this YTD.
The calculation used to work when the IsYTD was a column and I did not have to use a Filter. Since there are a lot of calculations behind the IsYTD filter, I had to change it to a measure. It has not worked since. Do I need to do something extra when I use a Filter in the measure for All to work?
