Count of items based on slicer results

[cdcarnes]

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:

Primary Coverage Plan =

    VAR MaxStartDate =

        CALCULATE(

            MAX(FactClientCoveragePlan[StartDate]),

            FactClientCoveragePlan[StartDate] < MAX('dwh DimDate'[SmallDate]) &&

            FactClientCoveragePlan[ClientID] = SELECTEDVALUE('dwh DimClient'[ClientID])

        )

    VAR

        NoCoveragePlan =

            CALCULATE(

                CALCULATE(

                    COUNTROWS(FactClientCoveragePlan),

                    FactClientCoveragePlan[ClientID] = SELECTEDVALUE('dwh DimClient'[ClientID])

                ),

                CROSSFILTER(FactClientCoveragePlan[CoveragePlanID], 'dwh DimCoveragePlan'[CoveragePlanId], none)

            )

    VAR

        CoveragePlanID =

          IF(

            NoCoveragePlan < 1,

               IF(

                "Self-Pay" in ALLSELECTED('dwh DimCoveragePlan'[CoveragePlanName]),

                0,

                -10),

              CALCULATE(

                        MAX(FactClientCoveragePlan[CoveragePlanID]),

                        FactClientCoveragePlan[StartDate] = MaxStartDate &&

                        FactClientCoveragePlan[ClientID] = SELECTEDVALUE('dwh DimClient'[ClientID]

                        )                

                    )

            )

    VAR

        CoveragePlanName =

            LOOKUPVALUE(

          'dwh DimCoveragePlan'[CoveragePlanName],

          'dwh DimCoveragePlan'[CoveragePlanId], CoveragePlanID)

    RETURN

      CoveragePlanName

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.  

Count Of Primary Coverage Plan =

    CALCULATE(

        DISTINCTCOUNT(FactClientCoveragePlan[ClientID]),

        FILTER(FactClientCoveragePlan,

            FactClientCoveragePlan[Coverage Plan Name] = SELECTEDVALUE('dwh DimCoveragePlan'[CoveragePlanName])

            &&

            [Active Enrollment] = 1

        )

)

Any help to point me in the right direction would be helpful.

Thanks in advance.  

[cdcarnes]

 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:

Count Of Primary Coverage Plan =

    VAR ClientCoveragePlan =

        CALCULATETABLE('dwh DimClient',

            FILTER('dwh DimClient',

                'dwh DimClient'[MilitaryStatus] = "Yes"

                &&

                [Active Enrollment] = 1

                &&

                [Primary Coverage Plan] = SELECTEDVALUE('dwh DimCoveragePlan'[CoveragePlanName])

            )

        )

    Return

            COUNTROWS(ClientCoveragePlan)

here is the bar graph that is using this measure:

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.

VAR

        CoveragePlanID =

          IF(

            NoCoveragePlan < 1,

               IF(

                "Self-Pay" in ALLSELECTED('dwh DimCoveragePlan'[CoveragePlanName]),

                0,

                -10),

                CALCULATE(

                    CALCULATE(

                        MAX(FactClientCoveragePlan[CoveragePlanID]),

                        FactClientCoveragePlan[StartDate] = MaxStartDate &&

                        FactClientCoveragePlan[ClientID] = SELECTEDVALUE('dwh DimClient'[ClientID]

                        )                

                    ),

                    CROSSFILTER(FactClientCoveragePlan[CoveragePlanID], 'dwh DimCoveragePlan'[CoveragePlanID], none)

                )

            )

Again, any help would be appreciated.

Thank you

[cdcarnes · Accepted 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.

VAR MaxStartDate =

        CALCULATE(

            CALCULATE(

                MAX(FactClientCoveragePlan[StartDate]),

                FactClientCoveragePlan[StartDate] < MAX('dwh DimDate'[SmallDate]) &&

                FactClientCoveragePlan[ClientID] = SELECTEDVALUE('dwh DimClient'[ClientID])

            ),

            CROSSFILTER(FactClientCoveragePlan[CoveragePlanID], 'dwh DimCoveragePlan'[CoveragePlanId], none)

        )

[lbendlin]

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?

[cdcarnes]

Sorry, I should have explained that.  Yes.  It is the plan with the maximum start date that falls within the selected date range.