Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
D_PBI
Post Patron
Post Patron

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

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

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

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:
Ocode.JPG

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

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:
out.JPG

How do I achieve this?

Thanks.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

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
Post Patron
Post Patron

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.