How to DISTINCTCOUNT a list of IDs from a SUMMARIZE and a list of IDs from a SUMMARIZECOLUMNS?

[D_PBI]

Hi,
I have two CALCULATEDTABLEs. The first CALCULATEDTABLE's code is below:


When placing the [Financial Year], [Quarter] and the created [Active Cases] fields into a table visual we can view the output as below:


A list of [caseid] with their [Financial Year] and [Quarter] values are shown for the filtering applied. At this point, please note the DISTINCTCOUNT of [Active Cases] for the [Financial Year] = 2021-22 and [Quarter] = 3 is 32.

The second CALCULATETABLE's code is below:


When placing the [caseid] field from the CALCULATETABLE into a table visual it shows a list of [caseid] for the filtering applied - see below:


The number of unqiue [caseid] in this second CALCULATETABLE is 1516 (I've counted them).

Now for my question, I need to append/union both list and produce a definitive count of both lists by Financial Year and Quarter.
For example, we know the DISTINCTCOUNT of [Active Cases] for Financial 2021-22 and Quarter 3 is 32. We know the DISTINCTCOUNT of [caseid] for the second CALCULATETABLE is 1516.  Appending/unioning both lists, at this point, would produce the single value of 1548. However, let's say there are 10 [Active Cases] that appear in the [caseid] list. I would like to remove these 10 duplicates so the over figure is 1538 (not 1548).

Let's say [Financial Year] = 2021-22 and [Quarter] = 2 returns a total of 200 [Active Cases]. I would need this value appending to the second CALCULATETABLE figure too (this would result in 1716), but this time the duplication between both lists is just 6 - therefore the overall count for [Financial Year] = 2021-22 and [Quarter] = 2 should be 1710.

I hope you understand the logic thus far.

The final output I need is shown below:


How do I achieve this?

Thanks.

[Icey · Accepted solution]

Hi @D_PBI ,

Try to use DISTINCT function like this:

Count =
COUNTROWS (
    DISTINCT (
        UNION ( DISTINCT ( 'Table1'[Column1] ), DISTINCT ( 'Table2'[Column1] ) )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_PBI]

Bump - and also to note that I've just amended my desired output screenshot with the correct numbering as it wasn't initially (fundamental in showing what I'm trying to achieve). Thanks.