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 Smiley Happy
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

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 3,082 guests
Please welcome our newest community members: