Help with a measure, forget relationship

[Anonymous]

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. 

[Anonymous]

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]

@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

[Anonymous]

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]

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]

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]

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]

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

[Anonymous]

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

Best

Darek