Forum Discussion
translate SQL code for unique count into Power BI Code unique code
Hi, Pattem,
I added one more criteria [TRASN_TYPE] = “INDEM” and tested the all the situations but changing NOT in differetn filtering colujmns but still got the number which is higher than the SQL result (the unique count is 923). The lowest number based on your code is 1,619.
Had a chance, could you take a look?
Appreciate your help.
Dennis
L16 CWOP DistinctCount =
VAR Exclusion =
SELECTCOLUMNS (
FILTER (
LossFile,
LossFile[RECORD_TYPE] = "P"
&& LossFile[TRANS_TYPE] = "INDEM"
&& LossFile[ACCOUNT_DATE] >= DATE ( 2019, 1, 1 )
),
"CLAIMANT", LossFile[CLAIMANT]
)
VAR Inclusion =
SELECTCOLUMNS (
FILTER (
SUMMARIZE (
FILTER (
LossFile,
LossFile[TRANS_TYPE] = "INDEM"
&& LossFile[ACCOUNT_DATE] < DATE ( 2019, 5, 1 )
),
LossFile[CLAIMANT],
"Total", SUM ( LossFile[AMOUNT] )
),
[Total] = 0
),
"CLAIMANT", LossFile[CLAIMANT]
)
RETURN
CALCULATE (
DISTINCTCOUNT ( LossFile[CLAIMANT] ),
FILTER (
LossFile,
(LossFile[CLAIMANT]) IN Exclusion
&& NOT(LossFile[CLAIMANT]) IN Inclusion
)
)
12Bowers12 It's always recommended to post sample test data and expected output for an accurate solution. Please try this.... which is not tested (without any sample data)
DistinctCount = VAR _Exclusion = SELECTCOLUMNS(FILTER(PolicyData,Account_Date >= "2019-01-01" & Record_Type = "P"),"Claimant",[Claimant]) VAR _Inclusion = SELECTCOLUMNS(FILTER(SUMMARIZE(FILTER(PolicyData,Account_Date < "2019-05-01"),Claimant,"Total",SUM(Amount)),[Total]>0),"Claimant",[Claimant]) RETURN CALCULATE(DISTINCTCOUNT(Claimant),FILTER(PolicyData,Claimant NOT IN _Exclusion & Claimant IN _Inclusion))
Thank you, Pattem, I am also testing your solution. I will paste data next time. Dennis