Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
company_id 1152 would not be included, as they have renewed their insurance type.
Here are the relevant fields:
Solved! Go to Solution.
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 ()
)
)
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
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 ()
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
27 | |
23 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |