cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
John_Arb Frequent Visitor
Frequent Visitor

Help with a measure, forget relationship

Hi everybody, 

 

I am trying to do a measure with a special condition. I have two tables, Job and JobLedgerEntry with a relation as One Job to Many JobLedgerEntry

I need a measure to know the max() of the field "JobLedgerEntry[PostingDate]" without the relation between the tables but with the filter (filter for using on the report) on the field JobLedgerEntry[PostingDate].

One solution could be delete the relation between the tables and use the function USERELATIONSHIP, but I have many measures depending of the relation. 

I can´t use LastDate because of it doest consider the filter on the field although it get to break the relation. 

 

Thanks, and sorry for my bad english. 

8 REPLIES 8
Super User
Super User

Re: Help with a measure, forget relationship

@John_Arb not fully sure about your requirment, do you want MAXIMUM date across all the jobs? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





John_Arb Frequent Visitor
Frequent Visitor

Re: Help with a measure, forget relationship

Hi @parry2k ,


The measure is:

Is closed = if((Job[ClosedDate] >= Min(JobLedgerEntry[PostingDate]) && Job[ClosedDate] <=Max(JobLedgerEntry[PostingDate]));"Closed" ;"Not closed"))
Max and Min need the same special condition.

The Relation is: 1 Job to Many JobLedgerEntry

Example:

Table Job
JobNo ClosedDate
11OD18009 30/04/2019
********************
Table JobLedgerEntry
JobNo PostingDate
11OD18009 31/12/2018
11OD18009 21/02/2019
*************************
Selected Filter on the report by the user:
PostingDate
05/10/2018..11/03/2019
*****************************
Expected date:
Max should be 11/03/2019 and not 21/02/2019. Then, how can I keep the filter but breaking the relation between tables for max and min ?



Thanks a lot.

Super User
Super User

Re: Help with a measure, forget relationship

Mate, paste a picture of the model in here because it looks like you're using different dates from different tables for different purposes and you don't seem to know what to filter to get this working.

 

Best

Dare

John_Arb Frequent Visitor
Frequent Visitor

Re: Help with a measure, forget relationship

I haven´t sent any images because all fields and table names are in spanish and the measure is more complex. I think is more clear with a simple example than to show you images. So we only need the relation between the tables and the problem with max() and PostingDate.

 

 

 

 

Super User
Super User

Re: Help with a measure, forget relationship

You said:

 

Max should be 11/03/2019 and not 21/02/2019.

 

Well, then you need to extract the max from the slicer and not the fact table.

 

Best

Darek

John_Arb Frequent Visitor
Frequent Visitor

Re: Help with a measure, forget relationship

What do you want to tell with slicer? and the fact table ? 

Super User
Super User

Re: Help with a measure, forget relationship

Please, learn a bit about the proper design: https://www.youtube.com/watch?v=pvIVMEFQokE

 

Best

Darek

Highlighted
John_Arb Frequent Visitor
Frequent Visitor

Re: Help with a measure, forget relationship

Hi, I could get the value with this calculate column 

"PostingDateMax = CALCULATE(Max(JobLedgerEntry[PostingDate]);ALLSELECTED())", but when y put it on the measure doesnt work.

 

PostingDateMin = CALCULATE(Min(JobLedgerEntry[PostingDate]);ALLSELECTED()).

 

original measure:
Is closed = if((Job[ClosedDate] >= Min(JobLedgerEntry[PostingDate]) && Job[ClosedDate] <=Max(JobLedgerEntry[PostingDate]));"Closed" ;"Not closed"))

 

new measure:
Is closed = if((Job[ClosedDate] >= JobLedgerEntry[PostingDateMin] && Job[ClosedDate] <= JobLedgerEntry[PostingDateMax]);"Closed" ;"Not closed"))

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)