Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |