cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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"))

 

parry2k
Super User III
Super User III

@Anonymous 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 Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

 

 

 

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Best

Darek

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors