Selecting rows with slicer doesn't work

[a_nijsten]

Hello,

I am stuck with a Power BI problem.

In the model there is a data table (Testdata) and a date table (Datetable). In the slicers, I have selected the year 2024 and the month of May. Based on these slicers, I would like to see all rows with a start date <= 31-05-2024 and an end date >= 01-01-2024 or empty. But it is not working.

I am only getting the rows with a start date in May, but I also want to include the rows that started before May and have an end date in or after May or are not filled in at all.

You could solve this problem by setting dates in the filters, but then you would have to adjust these with every change. Therefore, I want to solve this with a single click via a slicer.

Please find enclosed PBIX and expected result. https://we.tl/t-6LHcCD5Juj 

Who can help me with this?

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Ashish_Mathur  and @elitesmitpatel  and @foodd  provided, and i want to offer some more infotmation for user to refer to.

hi @a_nijsten , you can create a measure

MEASURE =
VAR a =
    MAX ( Datetable[Date] )
VAR b =
    EOMONTH ( a, -1 ) + 1
RETURN
    CALCULATE (
        COUNTROWS ( Testdata ),
        Testdata[Startdate] <= a,
        OR ( Testdata[Enddate] >= b, Testdata[Enddate] = BLANK () ),
        CROSSFILTER ( Datetable[Date], Testdata[Startdate], NONE )
    )

Outptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[a_nijsten]

Thank you for your help. This is also a useful solution!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[a_nijsten]

Thank you very much for your help! This is a great solution that I wouldn't have been able to come up with on my own. Additionally, this has helped to expand my knowledge. This isn't taught in courses.

[Ashish_Mathur]

You are welcome.

[foodd]

remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[a_nijsten]

Thanks. I didn't know how I could enclose the sample files. I have edit my question and enclosed a link with the sample files.

[elitesmitpatel]

Hey @a_nijsten the link is expired , please update the link and if possible share with Google drive or one drive .