Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BotBot1
Helper III
Helper III

Count Companies with a Category Expired

Hi,

 

I am trying to get a measure that is a distinct count of company_ids where they have at least 1 expired insurance document. It needs to only look at insurances with a record status of 1, and for it to look at the latest date of each insurance type (as they can have multiple of the same type with different dates) and count the company if it is expired.

 

So the count would be companies with an active status where at least 1 insurance type (given by the latest date of each category) is before today. For example:

company_id 19451 and 44074 would be counted.

BotBot1_0-1688128973623.png

 

company_id 1152 would not be included, as they have renewed their insurance type.

BotBot1_1-1688129135092.png

 

Here are the relevant fields:

vw_FT_Company_Insurance_BI[Company_id]
vw_FT_Company_Insurance_BI[insurance_recordStatus]
vw_FT_Company_Insurance_BI[Insurance_Type_Name]
vw_FT_Company_Insurance_BI[ExpiryDate]

Please let me know if any further information is required, thank you.
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @BotBot1 
Please try

Count =
COUNTROWS (
    FILTER (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    vw_FT_Company_Insurance_BI,
                    vw_FT_Company_Insurance_BI[insurance_recordStatus] = 1
                ),
                vw_FT_Company_Insurance_BI[Company_id],
                vw_FT_Company_Insurance_BI[Insurance_Type_Name],
                "@ExpiryDate", MAX ( vw_FT_Company_Insurance_BI[ExpiryDate] )
            ),
            vw_FT_Company_Insurance_BI[Company_id],
            "@MinExperyDate", MINX ( CURRENTGROUP (), [@ExpiryDate] )
        ),
        [@MinExperyDate] <= TODAY ()
    )
)

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

Try

Num expired =
VAR SummaryTable =
    INDEX (
        1,
        FILTER (
            vw_FT_Company_Insurance_BI,
            vw_FT_Company_Insurance_BI[insurance_recordStatus] = 1
        ),
        ORDERBY ( vw_FT_Company_Insurance_BI[ExpiryDate], DESC ),
        PARTITIONBY ( vw_FT_Company_Insurance_BI[Company_id],
        vw_FT_Company_Insurance_BI[Insurance_Type_Name] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( vw_FT_Company_Insurance_BI[Company_id] ),
        SummaryTable
    )
RETURN
    Result
tamerj1
Super User
Super User

Hi @BotBot1 
Please try

Count =
COUNTROWS (
    FILTER (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    vw_FT_Company_Insurance_BI,
                    vw_FT_Company_Insurance_BI[insurance_recordStatus] = 1
                ),
                vw_FT_Company_Insurance_BI[Company_id],
                vw_FT_Company_Insurance_BI[Insurance_Type_Name],
                "@ExpiryDate", MAX ( vw_FT_Company_Insurance_BI[ExpiryDate] )
            ),
            vw_FT_Company_Insurance_BI[Company_id],
            "@MinExperyDate", MINX ( CURRENTGROUP (), [@ExpiryDate] )
        ),
        [@MinExperyDate] <= TODAY ()
    )
)

Perfect, thank you so much @tamerj1 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.