Getting the values in Table visual comparing with multiple date column as per selected slicer value

[ak2812]

I've below data model :- 

CLNDR table is joined with fact on 3 different date columns of fact table
Enroll_date - Active relationship
Activa_date - InActive relationship
Cancl_date - InActive relationship
Now I've multiselect slicers coming from Clndr Table (Year_No , Month_No) and as per Year-Month selection data will be displayed on table visual on certain condition
(clndryear in @selected year OR Year(Activa_date) in @selected year  OR Year(Cancl_date) in @selected year )
AND 
(clndrmonth in @selected MONTH OR Month(Activa_date) in @selected year  OR Month(Cancl_date) in @selected year )
Sampling some expected output (if Year - 2025 & Month - 1&2 is selected in slicer)

Enroll Date	Activa Date	Cancl Date
05/02/2025	01/29/2019
06/20/2025	02/22/2015

 Explanation :- (for both record) As Enroll date is falling in selected year and Activa date is falling in selected month 
Any help is appreciated !

[ak2812]

Hi  @DataNinja777   Thanks for your response 
My model is on direct query and when i put this measure in Filter on visual option , it throws below error 

Note:- My Fact table contains around 1.3M data ! 

[v-lgarikapat]

Hi @ak2812 ,

Thanks for reaching out to the Microsoft fabric community forum.

@DataNinja777 , 

Thanks for your prompt response

@ak2812  Based on the screenshot you have shared, below are a few suggested workarounds. I have also added the learning documents along with previously resolved cases, which may help you understand the issue better and assist in resolving it.

Workaround Options:

1. Pre-filter the data using slicers or page-level filters to reduce the row count before the visual runs.
2. Convert the measure logic into a calculated column if possible and filter on that instead. Calculated columns are evaluated during model load, not at query time.
3. Use an aggregated table that summarizes the data at a higher grain (e.g., monthly totals, category-level summaries) and point your visual to that.
4. Optimize the measure to avoid full table scans consider using variables or limiting the context with FILTER () more selectively.

DirectQuery in Power BI - Power BI | Microsoft Learn

Troubleshoot DirectQuery models in Power BI Desktop - Power BI | Microsoft Learn

Optimization guide for Power BI - Power BI | Microsoft Learn

DirectQuery optimization scenarios with the Optimize ribbon in Power BI Desktop - Power BI | Microso...

Solved: Direct Query 1 million row limitation in Power BI ... - Microsoft Fabric Community

Thanks for being an active member of the community.

Best Regards,

Lakshmi.

[DataNinja777]

Hi @ak2812 ,

You can solve this by creating a DAX measure that you then use as a filter on your table visual. A standard relationship can't handle this logic because your condition involves an OR across three different date columns that are checked against the same slicer. The measure will manually perform this check for every row.

You'll need to create a new measure in your fact table using the following DAX code. Remember to substitute 'FactTable' with your table's actual name and ensure the year and month column names from your CLNDR table are correct.

ShowRow = 
VAR SelectedYears = VALUES('CLNDR'[Year_No]) -- Replace with your actual year column, e.g., clndr_yr_nbr
VAR SelectedMonths = VALUES('CLNDR'[Month_No]) -- Replace with your actual month column

-- Get the date values for the current row being evaluated in the visual
VAR CurrentEnrollDate = SELECTEDVALUE('FactTable'[Enroll_date])
VAR CurrentActivaDate = SELECTEDVALUE('FactTable'[Activa_date])
VAR CurrentCanclDate = SELECTEDVALUE('FactTable'[Cancl_date])

-- Check if any of the row's years match the selected years
VAR IsYearMatch = 
    (YEAR(CurrentEnrollDate) IN SelectedYears) || 
    (YEAR(CurrentActivaDate) IN SelectedYears) || 
    (YEAR(CurrentCanclDate) IN SelectedYears)

-- Check if any of the row's months match the selected months
VAR IsMonthMatch = 
    (MONTH(CurrentEnrollDate) IN SelectedMonths) || 
    (MONTH(CurrentActivaDate) IN SelectedMonths) || 
    (MONTH(CurrentCanclDate) IN SelectedMonths)

-- Return 1 only if BOTH the year AND month conditions are met, otherwise return BLANK
RETURN
    IF(
        IsYearMatch && IsMonthMatch, 
        1, 
        BLANK()
    )

Once you've created the ShowRow measure, do not add it as a column to your visual. Instead, select your table visual and drag the [ShowRow] measure into the Filters pane under the "Filters on this visual" section. In the filter card for the measure, set the condition to "is not blank" and apply the filter.

This method works by dynamically calculating the measure for every row in your visual. It checks if any of the row's three date columns contain a year that is present in your selected years slicer, and also checks if any of the dates contain a month present in your selected months slicer. The measure returns a 1 only if a row satisfies both the year and month conditions. By filtering the visual to show where the measure is not blank, you effectively display only the rows that meet your complex criteria.

Best regards,