Removefilters not working
Hi All, I'm writing a DAX Query which exectutes different parts of the query depending on the current filter. This logic works, however: In example #1, de removefilters works just fine. I'm getting values over a range of dates. In example #2, the removefilters does not work. The result is a value on a single date. It seems to be caused by the filter statement which i marked with as #3. Does anyone know if performing a removefilter together with another filter in the Calculate statement does't work? I can imagine the fact table inside #3 is still filtered by the date table. But how to get rid of the Date filter there? I've tried to work with ALLEXCEPT(FACT,COLUMN,ETC). But so far no result. Any help is appreciated.
non-default value in 'greater than' slicer removes removefilters
Hi, I have a table with dataset (below) In the table Production values are duplicated for each year, region, Line, primary_product. 1. I created measure to calculate production accross year, region, Line, primary_product and ignore product and unit slicers . Production_Meets = CALCULATE( SUMX( SUMMARIZE( Sheet1, Sheet1[year], Sheet1[region], Sheet1[Line], Sheet1[primary_product], "UniqueProduction", MAX(Sheet1[production]) ), [UniqueProduction] ), REMOVEFILTERS(Sheet1[unit], Sheet1[product]) ) Issue: When Production 'Greater than' slicer is set to default (30), the measure shows correct value 213 (production value for bikeA primary_product across all products - frame , wheel) When When Production 'Greater than' slicer is set to non default (0,1,2..29,31..), the measure shows correct value 213. Then the Production_meet loses Removefilter ability and shows 180 (production value for bikeA primary_product across all products - frame only) If I set "Value filter behavior" in Model properties as Independent, then the formula will work, but I do not want to make this change on model level, as it affects other reports. How to fix production_meets calculations? year region Line primary_product product unit production 2024 NA 123 bikeA wheel % 100.00 2024 NA 123 bikeA wheel kg 100.00 2024 NA 123 bikeA bikeA % 100.00 2024 NA 123 bikeA wheel each 100.00 2024 NA 123 bikeA frame % 100.00 2024 NA 123 bikeA frame kg 100.00 2024 NA 123 bikeA frame lb 100.00 2024 NA 123 bikeA frame each 100.00 2024 NA 123 bikeA frame tn 100.00 2024 NA 567 bikeA wheel % 50.00 2024 NA 567 bikeA wheel kg 50.00 2024 NA 567 bikeA wheel lb 50.00 2024 NA 567 bikeA bikeA % 50.00 2024 NA 567 bikeA frame % 50.00 2024 NA 567 bikeA frame kg 50.00 2024 NA 567 bikeA frame lb 50.00 2024 NA 567 bikeA frame each 50.00 2024 NA 567 bikeA frame tn 50.00 2024 NA 789 bikeA wheel % 30.00 2024 NA 789 bikeA wheel kg 30.00 2024 NA 789 bikeA wheel lb 30.00 2024 NA 789 bikeA wheel each 30.00 2024 NA 789 bikeA bikeA % 30.00 2024 NA 789 bikeA frame kg 30.00 2024 NA 789 bikeA frame lb 30.00 2024 NA 789 bikeA frame each 30.00 2024 NA 789 bikeA frame tn 30.00 2024 NA 333 bikeA wheel % 33.00 2024 NA 333 bikeA wheel kg 33.00 2024 NA 333 bikeA wheel lb 33.00 2024 NA 333 bikeA wheel each 33.00 2024 NA 333 bikeA bikeA % 33.00
Variance between Two Years on same Date Axis using same Amount Column
Unfiltered: Filtered: The task at hand is simple, take the 2025 values (Food Revenue, Beverage Revenue, etc.) and subtract the 2024 values from the correlated field (first screenshot). However, I am running into issues with the date axis it lies on and how to correctly display only the months needed. The formula used takes the sum of the current year (CY, Dec 2024 - Mar 2025) for a specific account, and subtracts it from the balance of the prior year (LY, Dec 2023 - Mar 2024). This does in fact work, as the values shown in the second screenshot are correct for Food Revenue Variance for the months needed thus far (fiscal year starts in December, so December 2024 - March 2025). However, when I filter to just show the months needed, the values revert to only show the balances correlated with the current year (CY). Any ideas on how to show this variance correctly? In layman's terms, basically need to INCLUDE data from all months relevant (Dec 2023 - Mar 2024, Dec 2024 - Mar 2025), but I only need to DISPLAY the months with the correct values for variance (Dec 2024 - Mar 2025). DAX measure: VARI Food Revenue = VAR CY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = 0) VAR LY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = -1, REMOVEFILTERS(DateDim[Date].[Year])) RETURN CY - LY
Consistent Ranking Measure Ignoring Page Slicers
I am trying to create a ranking measure in Power BI based on a specific variable while ensuring that the ranking remains consistent regardless of any slicers applied on the page. But the ranking changes when the slicer is applied. The ranking should remain the same irrespective of any slicers applied on the page (specifically, slicers for Category). I have tried ALL and ALLSELECTED but the rankings change when I applied the slicers. Here is the measure I used: rank = RANKX(ALL(Table),CALCULATE(SUM(Table[Income]),REMOVEFILTERS(Table[Category])),,DESC,Dense) When I apply the slicer, the ranking position decreases by one spot with this measure. How can I change this for it to work?
ALL() and REMOVEFILTER() Don't Work Correctly on My Report
Hi, I have a problem that showing Lifetime amount by using ALL() or REMOVEFILTERS() functions on my report. I have created a new date table as slicer that implies Yesterday, Last 7 Days, Same Day Last Month, All, Custom etc., and it works perfect. But when I selected any slicer rather than "All", Lifetime Revenue does not show correct amount. Since the new slicer table consists duplicate dates, the cross-filter direction on relationship is not single as below. The dax query, I've used is to get Life Time Revenue: Revenue_Lifetime = CALCULATE( SUM(Revenue[Revenue]), ALL('Date Periods'[Date],'Date Periods'[Type]) ) Here is a sample report that shows the problem, https://we.tl/t-Q0SBtDHTFn Thank you, Kisa
RemoveFilters not working as expected
I have a page level filter for DaysAgo>0. This allows my MTD LY /YTD LY time calculation filters to work as expected and not go through the full month of the previous year. I am now also trying to build a measure for the full previous month of the current year, but this filter prevents it from looking past the current day (for example, it's 2/9/24 and I want full month of January, but the DAysAgo filter only shows to 1/9/24). Does RemoveFilters not work on page level filter context? Is there another way to accomplish this? Net Sales PrevMonth = CALCULATE([Net Sales MTD],DATEADD('Calendar'[Date Value],-1,MONTH),REMOVEFILTERS('Calendar'[Days Ago]))
Few Slicers to work on measures and few are not
Hi, I am facing an issue with creation of DAX. I have got a request from the client where we have a slicer of an Item and Provider at the top of dashboard. I have to show all to KPI for that Item and Provider selected. Further I have all the other dimension filters like Country, City, Corporate group etc. Then I have to create a DAX wherein I have to find the median of my measure amount of the selected Item and Provider. Another measure I have to create is Median of Rest of the Providers amount that were not selected in that Provider slicer. All the dimension filter should work on the rest of the filters but not on the selected filter. Also, I have to show all the slicers so removing interaction is not helping. I have to write the DAX only. Any type of help would be very much appreciated. Thank you.Grand Total Not Correct after Using REMOVEFILTERS
Hi, I am new to Power BI but I am constantly learning. I have tried so many different ways (posts, Youtube, etc) of solving this issue and am having no luck. Any help would be greatly appreciated. I am trying to create a drill down to item level data to include items purchased by a vendor selected in the summary, the purchase amounts of those items and the sales amounts of those items. I used removefilters in the sales formula to remove the "source no." from the drill through filter. The source no is the vendor no or the customer no depending on the document type. This works fine for the line totals but the grand total is the sum of all items for the time period. Please see my pbix file here: TestFile
