Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I am having some trouble with a report that shows a clients primary coverage plan based on the date range selected.
I have the following tables
DimCoveragePlan
CoveragePlanId | CoveragePlanName | |
0 | Self-Pay | |
275 | Amerigroup | |
276 | Aetna | |
277 | Allied Benefit System | |
278 | Beacon Health Options, Inc | |
279 | Benefit Management | |
280 | Blue Cross Blue Shield | |
281 | Century Healthcare | |
282 | Cigna | |
283 | CK County Sheriff Dept | |
284 | Coventry | |
285 | Humana | |
286 | KHS Medicaid | |
287 | Mercy Health Plan | |
288 | Meritain Health |
FactClientCoveragePlan
ClientID | CoveragePlanID | StartDate | EndDate |
191 | 278 | 10/27/2021 0:00 | 2/10/2022 0:00 |
191 | 278 | 4/22/2022 0:00 | 7/20/2022 0:00 |
198 | 292 | 9/19/2017 0:00 | 9/19/2017 0:00 |
198 | 292 | 9/20/2017 0:00 | 3/13/2018 0:00 |
198 | 278 | 10/2/2019 0:00 | 10/31/2019 0:00 |
198 | 292 | 11/1/2019 0:00 | 10/31/2020 0:00 |
198 | 278 | 11/1/2020 0:00 | 12/3/2020 0:00 |
200 | 292 | 1/1/2013 0:00 | 6/30/2023 0:00 |
200 | 416 | 7/1/2023 0:00 | 1/1/2099 0:00 |
214 | 374 | 1/16/2020 0:00 | 4/8/2020 0:00 |
214 | 359 | 12/1/2020 0:00 | 1/24/2021 0:00 |
214 | 374 | 1/25/2021 0:00 | 8/4/2021 0:00 |
214 | 374 | 9/7/2021 0:00 | 4/4/2022 0:00 |
215 | 280 | 5/1/2020 0:00 | 2/28/2021 0:00 |
215 | 280 | 3/1/2021 0:00 | 8/31/2021 0:00 |
215 | 280 | 9/1/2021 0:00 | 5/31/2022 0:00 |
224 | 297 | 11/1/2017 0:00 | 8/31/2018 0:00 |
224 | 298 | 9/1/2018 0:00 | 9/30/2018 0:00 |
224 | 298 | 10/1/2018 0:00 | 11/6/2018 0:00 |
232 | 278 | 1/22/2014 0:00 | 1/6/2020 0:00 |
233 | 278 | 12/6/2018 0:00 | 10/22/2019 0:00 |
235 | 297 | 7/1/2017 0:00 | 4/2/2019 0:00 |
235 | 297 | 4/3/2019 0:00 | 4/14/2019 0:00 |
235 | 297 | 4/15/2019 0:00 | 6/23/2020 0:00 |
Both are connected via client ID.
I have a table on the page that is displaying the Client ID and their Primary Coverage Plan via the following measure:
As you may notice, "Self-Pay" does not show up in the FactClientCoveragePlan table. it is assigned to a client if they have no coverage plan listed.
The issue I am having is that there is a date range slicer on the page that lets you filter to any date range and a client may have mulitple coverage plans within that date range so I need to display only the most recent one that falls within that date range. I already have a measure that will calculate what the primary coverage plan to display is based on the date range but I am trying to add a bar graph that will provide a count by coverage plan. I tried adding the coverage plan name from the DimCoveragePlan table to the x axis and the following measure to the y axis but it is overcounting some clients because they had multiple different coverage plans in the date range selected. I need it to only count the most recent coverage plan.
Any help to point me in the right direction would be helpful.
Thanks in advance.
Solved! Go to Solution.
I figured it out. I was on the right track but the section i needed to remove filters from was the section of the [Primary Coverage Plan] measure that was calculating the Max Start Date. I made the following adjustment to it and it works now.
So I have made some progress on this. I am pretty sure that the issue is due to the filter context.
I reworked my measure to count the coverage plan as follows:
So, this measure works but is overcounting some of the coverage plans because it is not just counting the primary coverage plan. I am pretty sure that is due to the filter context. In other words, when the selected values is "Blue Cross Blue Shield" it is filtering the FactClientCoveragePlan table to only "Blue Cross Blue Shield" and then assigning the primarycoverageplan to the client ID.
This is the part of the [Primary Coverage Plan] measure that is assigning the Primary Coverage Plan to the client. I edited it from the original to try and get it to ignore the filter context coming from the slicer and the bar graph (both based on the DimCoveragePlan table) but it isn't working.
I figured it out. I was on the right track but the section i needed to remove filters from was the section of the [Primary Coverage Plan] measure that was calculating the Max Start Date. I made the following adjustment to it and it works now.
I need to display only the most recent one that falls within that date range
What's the definition of "most recent" ? Latest start date?
Sorry, I should have explained that. Yes. It is the plan with the maximum start date that falls within the selected date range.
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |