FILTER ACTIVE BENEFICIARIES FOR A CERTAIN PERIOD

[Matheuspvhdev]

Hi, everyone!

I have a table called "historico" on I record all status changes of the beneficiaries of a health care plan. Every beneficiarie can be status changed along the years. below I put an table example:

Historico Table

My big problem is: I need dynamically filter this table to get all records igual or less than a determinate date (e.g. 12/31/2019) and, with the result of the previous filter, for  get the record with the max date for each beneficiaries (represents for the column BEN_COD_BENEFICIARIO). Finally, I need filter the beneficiaries with status "A" after the last filter.

I provide, on this link, a .xlsx dump of my dataset to help you to understand my problem better.

Thanks for your help.

P.S.: I'm brazilian and my English is not so accurate. If you dont understand something, please ask me and I will try explain better. 

[AiolosZhao]

Hi @Matheuspvhdev ,

I have one question:

1. get the max data firstly then

2. filter status = "A".

So if a beneficiario's max data is status = "I', then you don't want to show the beneficiario, right?

Aiolos Zhao

[Matheuspvhdev]

Hi @AiolosZhao!

Yes, I just want the rows where the status is  "A". The other rows with status "I" doesn't matter for me.

Thank you so much for your help.

[AiolosZhao · Accepted solution]

Hi @Matheuspvhdev ,

I think you can use the below measure:

measure: calculate(max(date),status = "A")

and put the date into a slicer, and using the equal and less than mode for the date slicer.

Please try.

Aiolos Zhao

[Matheuspvhdev]

Thank you @AiolosZhao!

Your response help me to find a way to solve my problem. Thank you so much.

For the people who want know how resolve problems like this, I've created two measures:

Max Historico Date = 
VAR CurrentName = SELECTEDVALUE(HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO])

RETURN
MAXX(
    FILTER(ALLSELECTED(HIST_BENEFICIARIO), HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO] = CurrentName),
    HIST_BENEFICIARIO[BEN_DATA_INCLUSAO_CONTRATO]
)

Active per period = 
VAR tbConsolidada = SUMMARIZE(
            HIST_BENEFICIARIO, 
            HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO],
            "dtMax", [Max Historico Date]
    )
VAR tbwStatus = SUMMARIZE(
tbConsolidada, 
HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO], 
[dtMax], 
    "STATUS", LOOKUPVALUE(HIST_BENEFICIARIO[STATUS], HIST_BENEFICIARIO[BEN_DATA_INCLUSAO_CONTRATO], 
[dtMax], 
HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO], 
HIST_BENEFICIARIO[BEN_COD_BENEFICIARIO]
)
)
RETURN

CALCULATE(
    COUNTROWS(FILTER(tbwStatus, [STATUS] ="A"))
    
)

I put the "Active per period" measure in a card and, with a slicer, I'm can specific the limit date.

Thank you @AiolosZhao and @v-easonf-msft for your attention and help.

[AiolosZhao]

Glad to help you~

Aiolos Zhao

[v-easonf-msft]

Hi ,  @Matheuspvhdev 

I’m not sure what the "result from the previous filter" refers to, or the filtered table data?

Can you show the final result you want based on the data determinate date (e.g. 12/31/2019)?

It would help tremendously.

Best Regards,
Community Support Team _ Eason

[Matheuspvhdev]

Hi, @v-easonf-msft !

With the expression "result from the previous filter" I means the table Histórico after it  was filtred for records equal or less than the limit date (e.g. 12/31/2019). For example:

• I Have the follow dataset (dates in brazilian format: "dd/MM/yyyy hh:mm:ss"):

COD_BENEFICIARIO	STATUS	BEN_DATA
0000079000010	A	24/06/2010 00:00:00
0000079000010	I	13/10/2010 17:58:44
9902252500019	A	11/03/2019 00:00:00

• First, I need filter the records equal or less than 12/31/2019 and select just the max date for each COD_BENEFICIARIO. Something like this:

COD_BENEFICIARIO	STATUS	BEN_DATA
0000079000010	I	13/10/2010 17:58:44
9902252500019	A	11/03/2019 00:00:00

• With the "result from the previous filter", the table above, I've select just the rows where status equals to "A", in this case just the last row:

COD_BENEFICIARIO	STATUS	BEN_DATA
9902252500019	A	11/03/2019 00:00:00

That's the logic, but I don't know how implement this in PowerBI.

Thanks for your precious help.