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
Dunner2020
Post Prodigy
Post Prodigy

Measure does not return min date

Hi there,

 

I'm trying to create a measure that returns the minimum date if two or more records have the same ID and AuthorisationType is equal to 2 or 11. I wrote a measure which is as follows:

 

Min Date = CALCULATE(Min(Table[EffectiveDate]),FILTER(Table,Table[ID] = MAX(Table[ID]) &&(Max(Table[AuthorisationTypeID]) = 11 || Max(Table[AuthorisationTypeID]=2))))

 

The above measure does not return the desired result. For example, as shown in the following picture, record ID 108644 has two dates and the measure did not return the min date.

leo_89_0-1632882716965.png

 

 

Could anyone help me where I made the mistake?

 

Sample file here

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Drag ID to the visual and write this measure:

Min Date = CALCULATE(Min(Table[EffectiveDate]),Table[AuthorisationTypeID] = 11 ||Table[AuthorisationTypeID]=2)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@Dunner2020  please try this

 

Measure3 = 
MINX (
    FILTER ( 'Table', 'Table'[AuthorisationTypeID] IN { 2, 11 } ),
    VAR _0 =
        CALCULATE ( MAX ( 'Table'[ID] ) )
    VAR _1 =
        CALCULATE ( MIN ( 'Table'[EffectiveDate] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    VAR _2 =
        CALCULATE (
            MIN ( 'Table'[EffectiveDate] ),
            TREATAS ( { ( { _0 }, { _1 } ) }, 'Table'[ID], 'Table'[EffectiveDate] )
        )
    RETURN
        _2
)

 

smpa01_0-1632885472519.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Ashish_Mathur
Super User
Super User

Hi,

Drag ID to the visual and write this measure:

Min Date = CALCULATE(Min(Table[EffectiveDate]),Table[AuthorisationTypeID] = 11 ||Table[AuthorisationTypeID]=2)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.