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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

DAX Filter Context Issue

I have a Power BI Table with a category and a single calculated value.  In order to properly calculate the value, the underlying recordset needs to be filtered and ranked dynamically based upon user input.  However, the filter context created by the category in the table creates erroneous results in the ranking because by limiting the records it precludes the ranking from working properly (the ranking has to work on all records, not just the subset).  Therefore, the filter context for the category must be turned off prior to ranking.  When this is done using the ALL keyword, the results calculate properly but once the filter context is lost, the values returned to the visual show up for all categories.


The ranking, filtering, and calculations are fairly complex so they are handled through several table variables which in turn feed the resulting returned value.  For readability and clarity, I have tried to keep the process organized via the variables but I can't seem to find a simple way to replace the original filter context once the ranking is complete.  


Specifically, I need to turn off the Filter Context for VAR Cova, calculate the ranking, limit the recordset to the most current records based on the selected value, and then between VAR Cov2 and VAR Cov3, once the records are ranked and limited, I need to reapply the visualization's filter context.  It feels like I need to find some way to apply either a KeepFilters or AllSelected Values to Cov3, but I can't figure out how to do that. [Self] is a predefined measure that is essentially a distinct count of a value in the table tbl_Coverages.


VAR ElgMth = SelectedValue(Dim_EligMonth[EligMonth])

VAR Cova = Filter(All(tbl_coverages), [filedate] <= ElgMth)

VAR Cov1 =
AddColumns(Cova, "FileDateRanking",
VAR Mbr = tbl_coverages[memberregistrationid]
VAR BenType = tbl_coverages[BenefitType]
VAR Sub = tbl_coverages[parentregistrationid]
RANKX(Filter(Cova, tbl_coverages[memberregistrationid] = Mbr && tbl_coverages[parentregistrationid] = Sub && tbl_Coverages[BenefitType] = BenType) , [filedate],,DESC))

VAR Cov2 = Filter(Cov1, [FileDateRanking] = 1)
VAR Cov3 = Filter(Cov2, ElgMth >= [coverageenrollmentdate]
&& (ElgMth < [coverageterminationdate] || Trim([coverageterminationdate]) = "")
&& [planname] <> "Waiver"

Calculate([Self], Cov3)

Community Champion
Community Champion

@keobrie  You need to share your PBI file to get solution for this problem, it is impossible to tell if you are referring to a Meaure or a Column in your variables 🙂

Unfortunately the model itself is confidential and stored in an SSAS Tabular Database.  That said, your point is understood.  I am going to try to build out a "test" environment in PBI Desktop and post the test environment.  It may take me some time to get to it this week.  In the meantime, I used T-SQL to build out a different dataset that works for this specific use-case but I'd really like to see the solution handled dynamically in PBI.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors