Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
cdcarnes
Frequent Visitor

Count of items based on slicer results

‎04-01-2024 12:02 PM

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

CoveragePlanIdCoveragePlanName
0Self-Pay 
275Amerigroup
276Aetna 
277Allied Benefit System
278Beacon Health Options, Inc
279Benefit Management
280Blue Cross  Blue Shield
281Century Healthcare
282Cigna 
283CK County Sheriff Dept
284Coventry 
285Humana 
286KHS Medicaid
287Mercy Health Plan

288

Meritain Health

 

 

FactClientCoveragePlan

ClientIDCoveragePlanIDStartDateEndDate
19127810/27/2021 0:002/10/2022 0:00
1912784/22/2022 0:007/20/2022 0:00
1982929/19/2017 0:009/19/2017 0:00
1982929/20/2017 0:003/13/2018 0:00
19827810/2/2019 0:0010/31/2019 0:00
19829211/1/2019 0:0010/31/2020 0:00
19827811/1/2020 0:0012/3/2020 0:00
2002921/1/2013 0:006/30/2023 0:00
2004167/1/2023 0:001/1/2099 0:00
2143741/16/2020 0:004/8/2020 0:00
21435912/1/2020 0:001/24/2021 0:00
2143741/25/2021 0:008/4/2021 0:00
2143749/7/2021 0:004/4/2022 0:00
2152805/1/2020 0:002/28/2021 0:00
2152803/1/2021 0:008/31/2021 0:00
2152809/1/2021 0:005/31/2022 0:00
22429711/1/2017 0:008/31/2018 0:00
2242989/1/2018 0:009/30/2018 0:00
22429810/1/2018 0:0011/6/2018 0:00
2322781/22/2014 0:001/6/2020 0:00
23327812/6/2018 0:0010/22/2019 0:00
2352977/1/2017 0:004/2/2019 0:00
2352974/3/2019 0:004/14/2019 0:00
2352974/15/2019 0:006/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.  

Labels:
Message 1 of 5
150 Views
0
1 ACCEPTED SOLUTION
cdcarnes
Frequent Visitor
In response to cdcarnes

‎04-02-2024 09:39 AM

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)
        )

View solution in original post

Message 5 of 5
97 Views
4 REPLIES 4
cdcarnes
Frequent Visitor

‎04-02-2024 09:22 AM

 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:
 
cdcarnes_0-1712074631502.png

 

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
 
           
Message 4 of 5
98 Views
0
cdcarnes
Frequent Visitor
In response to cdcarnes

‎04-02-2024 09:39 AM

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)
        )
Message 5 of 5
98 Views
lbendlin
Super User
Super User

‎04-01-2024 01:58 PM 
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?

Message 2 of 5
121 Views
0
cdcarnes
Frequent Visitor
In response to lbendlin

‎04-01-2024 02:01 PM

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

Message 3 of 5
118 Views
0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All