Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering problem (I think)

I work for a housing charity. The data is confidential, so it's a bit difficult for me to post examples.

We do a monthly property check (known as an MPR) and I need to show whether a report has not been done in the previous month.

We have a number of properties across the country looked after by different centres. There is a one to many replationship between each of Centres->Properties->Questionnaires.

I can easily create a matrix with Centre/Property/Lastest MPR Date using this Measure:

Latest MPR Date = max('pbi Questionnaires'[DateOfCheck])
However if I try to add a column using a formula which basically tells me whether the date is after the beginning of the previous month using something like this:
MPR Date OK = if ([Latest MPR Date] < [First Day of Prev Month],0,1)
Then the matrix helpfully displays the address of every property under every Centre!
So like:
Centre 1
 House 1 from Centre 1
 House 2 from Centre 2
 House 3 from Centre 4
Centre 2
 House 1 from Centre 1
 House 2 from Centre 2
 House 3 from Centre 4
I am pretty sure that this is a filter issue of some kind or another, but Filters and I don't get on very well!
I have tried various filters and options using CALCULATE, but I can't work out what I am doing wrong.
5 REPLIES 5
Pragati11
Super User
Super User

HI @Anonymous ,

 

Create some sample data and share explaining issue. It's not very clear from your description.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

OK, I'll try to explain:

 

We have Centres:

 

Centre 1

Centre 2

Centre 3

Centre 4

 

We have Houses:

 

House 1 in Centre 1

House 2 in Centre 1

House 3 in Centre 2

House 4 in Centre 2

House 5 in Centre 3 

etc

 

Joined by Centre Code on a one-to-many relationship

 

Each House has a number of Questionnaire records with the date of the last inspection:

 

House 1 01/04/20

House 1 02/05/20

House 2 03/04/20

House 2 05/06/20

House 3 <no report done so no questionnaire record>

 

Joined by House Code on a one to many relationship

 

I can get the last house check done date with this measure:

 

Latest MPR Date = max('pbi Questionnaires'[Answer26]) (Answer 26 is the field name - I tried to simplify above)
 
When I add that to the Matrix I get the following:
 
Screengrab.png
 
I want to be able to Conditional Format this date column when the last date is either blank or where the date is earlier than the first of the previous month. Therefore I am attempting to create a column with a 1 or a 0 in it to do that.
However whatever measure I can come up with I end up with all the houses under Cambridge, all the houses under Norwich and all the houses under Nottingham displaying.
I am assuming this is a filtering issue, but I am completely at a loss to understand what I need to do.
Thanks for your help.

Hi @Anonymous 

 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Greg_Deckler
Super User
Super User

@Anonymous - Not really all that clear. What is your [First Day of PrevMonth] calculation?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Yes, sorry.

It's a measure:

First Day of Prev Month =
IF (
MONTH ( TODAY () ) = 1,
DATE ( YEAR ( TODAY () ) - 1, 12, 1 ),
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.