Measure filter for a table based on 2 slicers

[PLHM]

Good day

I have a report whereby I have 2 slicers. One with a date range and another with a single select drop down. I want to be able to filter my results as per the attempted measure below:

Is MergedRS Row Visible =
VAR SelectedDateType = SELECTEDVALUE(DateSelectionType[Date Type])
VAR MinSelectedDate = MIN(Dates[Date])
VAR MaxSelectedDate = MAX(Dates[Date])

RETURN
SWITCH(
SelectedDateType,
"Complete",
VAR CurrentCompletedDate = MAX(MergedRS[CompletedDate]) -- Use MAX (or MIN) to get the value for the current row context
RETURN
IF( (NOT ISBLANK(CurrentCompletedDate) && CurrentCompletedDate >= MinSelectedDate && CurrentCompletedDate <= MaxSelectedDate) || ISBLANK(CurrentCompletedDate), 1, 0 ),

"Initiated",
VAR CurrentInitDate = MAX(MergedRS[Init Date]) -- Use MAX (or MIN)
RETURN
IF( NOT ISBLANK(CurrentInitDate) && CurrentInitDate >= MinSelectedDate && CurrentInitDate <= MaxSelectedDate, 1, 0 ),

"Complete & Initiated",
VAR CurrentCompletedDate_Combined = MAX(MergedRS[CompletedDate]) -- Use MAX (or MIN)
VAR CurrentInitDate_Combined = MAX(MergedRS[Init Date]) -- Use MAX (or MIN)
RETURN
IF(
(
(NOT ISBLANK(CurrentCompletedDate_Combined) && CurrentCompletedDate_Combined >= MinSelectedDate && CurrentCompletedDate_Combined <= MaxSelectedDate)
|| ISBLANK(CurrentCompletedDate_Combined)
)
||
(
NOT ISBLANK(CurrentInitDate_Combined) && CurrentInitDate_Combined >= MinSelectedDate && CurrentInitDate_Combined <= MaxSelectedDate
),
1,
0
),
0
)

What I did further was return the MAX(MergedRS[CompletedDate])  and MAX(MergedRS[Init Date])  however these always come back BLANK regardless of what I attempt to do, and this no doubt is what is causing the measure to essentially fail. Any advice how I can get through this?

[PLHM · Accepted solution]

The amount of data that I have in my report and then with that the visuals to then change to make it anonymous data and the time this would take I did a little more research and found using a parameter I could swith between the 2 date options I wanted to filter on and that worked for been able to toggle between the 2 options of which date to filter on. I am still struggling with been able to filter by either of the dates, but for now it should be "good enough"

[v-kpoloju-msft]

Hi @PLHM,
Thank you for reaching out to the Microsoft fabric community forum. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[v-kpoloju-msft]

Hi @PLHM,

We haven’t received a response with the requested sample data to help troubleshoot your issue effectively.

To proceed further, please share a minimal and clean sample dataset (not a screenshot) along with the expected output. This helps the community understand and resolve your question faster.

Thank you.

[v-kpoloju-msft]

Hi @PLHM,

Following up again as we still haven't received the required sample data or clarification to assist with your issue. Please provide relevant sample data based on your thread.

Thank you.

[PLHM · Accepted solution]

The amount of data that I have in my report and then with that the visuals to then change to make it anonymous data and the time this would take I did a little more research and found using a parameter I could swith between the 2 date options I wanted to filter on and that worked for been able to toggle between the 2 options of which date to filter on. I am still struggling with been able to filter by either of the dates, but for now it should be "good enough"

[v-kpoloju-msft]

Hi @PLHM,

I hope this solution has resolved your issue. If so, please mark your post as an accepted solution, and mark the relevant @mark_endicott, post as accepted solution to assist other members in finding it more easily.

Thank you for participating in the Microsoft Community Forum.

[mark_endicott · Accepted solution]

@PLHM - OK this information indicates you are trying to slice with two date columns from the same table. In which case it's no wonder your measures are returning blank. Your date filtering is counteracting each other and reducing the rows to 0. 

As well as the parameter I would suggest implementing one or more date tables from which you can remove / change filter context in different situations. This  can be achieved via DAX code or a parameter as you say. 

If you implement one date table for both columns, make sure the relationships are in-active and you use USERELATIONSHIP inside CALCULATE measures to activate the relationships when necessary. 

@v-kpoloju-msft - if you are looking to close this one off as solved, I suggest we do so with this response. It is the best we can do in this situation. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mark_endicott]

@PLHM - Your initial Min and Max varaibles are the issue, because in a measure they are calculating at the row level, rather than over the whole table, you can fix this by removing the row context inside a calculate. Hopefully the code below fixes this, although some sample data would help me test it:

VAR SelectedDateType =
    SELECTEDVALUE ( DateSelectionType[Date Type] )
VAR MinSelectedDate =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
VAR MaxSelectedDate =
    CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
RETURN
    SWITCH (
        SelectedDateType,
        "Complete",
            VAR CurrentCompletedDate =
                MAX ( MergedRS[CompletedDate] ) -- Use MAX (or MIN) to get the value for the current row context
            RETURN
                IF (
                    (
                        NOT ISBLANK ( CurrentCompletedDate )
                            && CurrentCompletedDate >= MinSelectedDate
                            && CurrentCompletedDate <= MaxSelectedDate
                    )
                        || ISBLANK ( CurrentCompletedDate ),
                    1,
                    0
                ),
        "Initiated",
            VAR CurrentInitDate =
                MAX ( MergedRS[Init Date] ) -- Use MAX (or MIN)
            RETURN
                IF (
                    NOT ISBLANK ( CurrentInitDate )
                        && CurrentInitDate >= MinSelectedDate
                        && CurrentInitDate <= MaxSelectedDate,
                    1,
                    0
                ),
        "Complete & Initiated",
            VAR CurrentCompletedDate_Combined =
                MAX ( MergedRS[CompletedDate] ) -- Use MAX (or MIN)
            VAR CurrentInitDate_Combined =
                MAX ( MergedRS[Init Date] ) -- Use MAX (or MIN)
            RETURN
                IF (
                    (
                        (
                            NOT ISBLANK ( CurrentCompletedDate_Combined )
                                && CurrentCompletedDate_Combined >= MinSelectedDate
                                && CurrentCompletedDate_Combined <= MaxSelectedDate
                        )
                            || ISBLANK ( CurrentCompletedDate_Combined )
                    )
                        || (
                            NOT ISBLANK ( CurrentInitDate_Combined )
                                && CurrentInitDate_Combined >= MinSelectedDate
                                && CurrentInitDate_Combined <= MaxSelectedDate
                        ),
                    1,
                    0
                ),
        0
    )

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[PLHM]

Hi

Thank you, however the initial MIN and MAX were working just fine, as they are now, they are for the slicer values to be used to filter in the table. The Init Date and CompletedDate are the ones that don't appear to be working / getting a value and still don't get the value for some reason. Any idea why the Init Date and CompletedDate is not been picked up?

[mark_endicott]

@PLHM - Ok then I misunderstood where you need to remove the row context (this is still the issue). Here's the updated code to fix the issues with Init Date and CompletedDate:

VAR SelectedDateType =
    SELECTEDVALUE ( DateSelectionType[Date Type] )
VAR MinSelectedDate =
    MIN ( Dates[Date] )
VAR MaxSelectedDate =
    MAX ( Dates[Date] )
RETURN
    SWITCH (
        SelectedDateType,
        "Complete",
            VAR CurrentCompletedDate =
                CALCULATE ( MAX ( MergedRS[CompletedDate] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN) to get the value for the current row context
            RETURN
                IF (
                    (
                        NOT ISBLANK ( CurrentCompletedDate )
                            && CurrentCompletedDate >= MinSelectedDate
                            && CurrentCompletedDate <= MaxSelectedDate
                    )
                        || ISBLANK ( CurrentCompletedDate ),
                    1,
                    0
                ),
        "Initiated",
            VAR CurrentInitDate =
                CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN)
            RETURN
                IF (
                    NOT ISBLANK ( CurrentInitDate )
                        && CurrentInitDate >= MinSelectedDate
                        && CurrentInitDate <= MaxSelectedDate,
                    1,
                    0
                ),
        "Complete & Initiated",
            VAR CurrentCompletedDate_Combined =
                CALCULATE ( MAX ( MergedRS[CompletedDate] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN)
            VAR CurrentInitDate_Combined =
                CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN)
            RETURN
                IF (
                    (
                        (
                            NOT ISBLANK ( CurrentCompletedDate_Combined )
                                && CurrentCompletedDate_Combined >= MinSelectedDate
                                && CurrentCompletedDate_Combined <= MaxSelectedDate
                        )
                            || ISBLANK ( CurrentCompletedDate_Combined )
                    )
                        || (
                            NOT ISBLANK ( CurrentInitDate_Combined )
                                && CurrentInitDate_Combined >= MinSelectedDate
                                && CurrentInitDate_Combined <= MaxSelectedDate
                        ),
                    1,
                    0
                ),
        0
    )

Again it's really hard to test this without sample data, so if it's still not correct, then you will need to supply that so we can see what is not working.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[PLHM]

I tried this code and unfortuantely it still returns blank. I did some debugging by essentially commenting out the whole switch section and placing only 

CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) as the return, and this returns blank, which no dounbt is the problem but why it is returning blank when on that row there is a value I do not understand

[mark_endicott]

@PLHM - Neither will anyone on this forum until you give us some sample data and an idea of the visualisation you are trying to create. 

Until you do this your issue cannot be diagnosed. 

[Akash_Varuna]

Hi @PLHM MAX(MergedRS[CompletedDate]) and MAX(MergedRS[Init Date]) are evaluated in the current row context. When used in a measure, MAX or MIN operates over the filtered context provided by the slicers and other filters applied to the visual. If no context exists for these fields, MAX returns BLANK.

To fix this use CALCULATE with REMOVEFILTERS to explicitly alter context for MAX or MIN evaluations, preventing BLANK results.

[freginier]

Hi 

The issue is you're using MAX(MergedRS[CompletedDate]) and MAX(MergedRS[Init Date]) in row context, but those functions require a filter context—and since you're likely using this measure in a visual like a table, there's no row context being passed in automatically.

To fix it, wrap your logic in an iterator like MAXX to evaluate per row:

VAR CurrentCompletedDate = MAXX(VALUES(MergedRS[ID]), MergedRS[CompletedDate])

Replace MergedRS[ID] with a column that uniquely identifies rows. Do the same for Init Date.

This forces evaluation per row, and should return the correct value instead of BLANK.

freginier

[PLHM]

Unfortuantely that results in the following error:
A single value for column 'CompletedDate' in table 'MergedRS' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.