Forum Discussion
Selecting rows with slicer doesn't work
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!
Hi,
I have solved a similar problem in the attached PBI file.
Hope this helps.
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.
8 Replies
- foodd
Community Champion
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-Forum/ba-p/963216
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/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.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
Solution Supplier
Hey a_nijsten the link is expired , please update the link and if possible share with Google drive or one drive .
- Ashish_Mathur
Super User
Hi,
I have solved a similar problem in the attached PBI file.
Hope this helps.
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.
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.
Thank you for your help. This is also a useful solution!
