Forum Discussion
Counting distinct list against multiple contain strings
Looking for some assistance. I'm a novice 🙂
I need to count instruments within a set of specific types and for a specific financial year IF they have also have a specific set of offences listed. The offences are concatenated into a single list. An instrument (as represented by an instrument number, e.g. K010200) may have more than one of these offences but I only want to count an instrument once.
I thought the approach I finally landed on worked. But... it double counts if an instrument matches against more than one of the legislation references. I have 49 measure in total - some only have 1 reference to match against while several have 7.
*Please note the legislation references and instrument types shown below are publically available within our differing sets of legislation so I am not sharing any content that breaches any level of security etc.
MEASUREA1=
Hi Peterdown
Double counting is because you are calculating A, B, and C separately then adding. Instrument matching both 5D(1)(c) and 5D(5A) gets counted once in A and once in B. You can combine with OR in a CALCULATE where each instrument is only counted once
MEASUREA1 =
CALCULATE(
DISTINCTCOUNT('prod extract'[instrument_number]),
LEFT('prod extract'[instrument_type],3) IN {"s46","s48"},
'prod extract'[issued_date_financial_year] IN VALUES(refdata[current_year]),
FILTER(
'prod extract',
CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(1)(c)")
|| CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(5A)")
|| CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(2)(a)(b)(iv)")
)
)
For your other measures with up to 7 references, add more || conditions inside FILTER
2 Replies
Hi Peterdown
Double counting is because you are calculating A, B, and C separately then adding. Instrument matching both 5D(1)(c) and 5D(5A) gets counted once in A and once in B. You can combine with OR in a CALCULATE where each instrument is only counted once
MEASUREA1 =
CALCULATE(
DISTINCTCOUNT('prod extract'[instrument_number]),
LEFT('prod extract'[instrument_type],3) IN {"s46","s48"},
'prod extract'[issued_date_financial_year] IN VALUES(refdata[current_year]),
FILTER(
'prod extract',
CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(1)(c)")
|| CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(5A)")
|| CONTAINSSTRINGEXACT('prod extract'[offence_list],"5D(2)(a)(b)(iv)")
)
)
For your other measures with up to 7 references, add more || conditions inside FILTER
Thanks for the assistance. I can now understand where to place the FILTER function within an existing set of conditions. 🙂
