Forum Discussion

ldmass's avatar
ldmass
Regular Visitor
3 years ago
Solved

Get last sequences for a value in date range

Hi Experts, I'm having trouble solving an issue that seems simple but I can't find the solution. I have a table where I have the following columns: Sinister Sequence Date Patient Episode Ty...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    HI  ldmass ,

    Adjust to the below:

    TEST = var maxdate=CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )  var pd1= IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )
        = MAX ( 'Table'[Date] ),
    1,
    0
) var pd2=IF(MAX('Table'[Sequence])=
CALCULATE (
        MAX ( 'Table'[Sequence]),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )&&'Table'[Date]= maxdate)),1,0)  return  IF(PD1=1&&pd2=1,1,0)

    Result:

     

     

    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity Support
3 years ago

HI  ldmass ,

Pls use the below measure ,then filter the measure:

TEST = 
IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )
        = MAX ( 'Table'[Date] ),
    1,
    0
)

Output result:(the second slicer ,with two same last date on 1001,so with the two result)

 

 

 

Best Regards

Lucien

 

ldmass's avatar
ldmass
Regular Visitor
3 years ago

Hi Lucien,

Thanks you for your answer. In the second image, only sequence 3 should be displayed, since it is the largest, regardless of the date. For the selected date range, only the largest sequence should be displayed

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    HI  ldmass ,

    Adjust to the below:

    TEST = var maxdate=CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )  var pd1= IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )
        = MAX ( 'Table'[Date] ),
    1,
    0
) var pd2=IF(MAX('Table'[Sequence])=
CALCULATE (
        MAX ( 'Table'[Sequence]),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )&&'Table'[Date]= maxdate)),1,0)  return  IF(PD1=1&&pd2=1,1,0)

    Result:

     

     

    Best Regards

    Lucien