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

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,