Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ()
)
)
User | Count |
---|---|
48 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
102 | |
55 | |
28 | |
18 | |
13 |