Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
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.
Glad to help you~
Aiolos Zhao
Proud to be a Super User!
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
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:
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 |
COD_BENEFICIARIO | STATUS | BEN_DATA |
0000079000010 | I | 13/10/2010 17:58:44 |
9902252500019 | A | 11/03/2019 00:00:00 |
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |