March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I want to be able to Count the distinct values in the Claim Ref field of all the records where the ClassofBusinessCode = MV
Policycode contains MV
And the Type Code = R1
For Year to Date (Fiscal Year so 01/04/2023 to today – 02/08/2023)
I have a date table where the field to 'INS_Fin_Calendar Main Claim Data Payment Date'[Date] is linked to the INS_Main Claim Data [Date] fields are linked.
I was given the Measure below but it is calculating 11 records when there is actually only 2 distinct records
Can anyone see where I might be going wrong with the measure please or suggest another way of writing this?
z_Recoveries_Fiscal_YTD Recoveries No Successful Recoveries Motor =
VAR LastDayAvailable =
MAX ( 'INS_Fin_Calendar Main Claim Data Payment Date'[Date] )
VAR LastFiscalYearAvailable =
MAX ( 'INS_Fin_Calendar Main Claim Data Payment Date'[FiscalMIndex] )
VAR Result =
CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV" )
&& 'INS_Main Claim Data'[TypeCode]="R1"
),
'INS_Fin_Calendar Main Claim Data Payment Date'[Date] <= LastDayAvailable,'INS_Fin_Calendar Main Claim Data Payment Date'[Fiscal Year]=YEAR(TODAY()-365) & "-" & FORMAT(TODAY (), ("yyyy"))
)
RETURN
Result
@mlsx4 @Pragati11 @tamerj1 @Greg_Deckler
Solved! Go to Solution.
Hi @spandy34 ,
Make sure that the date format in the function matches the date format used in your data model. In your case, it should be "04/01"
Please try:
CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
&& 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04/01")
)
Or:
CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
&& 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04-01")
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you that has worked.
@spandy34 Probably an issue because you have a single table involved.
Could you see any reason why the following wouldnt work? Please excuse my knowledge. I am quite new to Meausres
I have a Date Table and also a Main Data Table which are linked so I dont have a single table.
Hi,
Share the download link of the PBI file.
Hi there thank you for responding. I wont be able to share the link of the PBI file for security reasons.
Could you see any reason why the following wouldnt work? I placed the start date of the year and the data seems to work but I didnt want to get caught out if you felt there would be an issue with the next financial year . Please excuse my knowledge. I am quite new to Meausres
Hi @spandy34 ,
Make sure that the date format in the function matches the date format used in your data model. In your case, it should be "04/01"
Please try:
CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
&& 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04/01")
)
Or:
CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
&& 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04-01")
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |