Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
This looks simple and but i tried differently and nothing works for me. Here below i explained the scenario by using sample data
My input data table structure
QuarterIn/Out FlagID
2010-1 | IN | 1 |
2010-2 | IN | 2 |
2010-3 | IN | 3 |
2010-4 | IN | 4 |
2011-1 | IN | 5 |
2011-2 | IN | 6 |
2011-3 | IN | 7 |
2011-4 | IN | 8 |
2012-1 | IN | 9 |
2012-2 | IN | 10 |
2012-3 | IN | 11 |
2012-4 | IN | 12 |
2010-2 | OUT | 13 |
2010-3 | OUT | 14 |
2011-1 | OUT | 15 |
2011-2 | OUT | 16 |
2011-3 | OUT | 17 |
2011-4 | OUT | 18 |
2012-1 | OUT | 19 |
2012-2 | OUT | 20 |
2012-3 | OUT | 21 |
2012-4 | OUT | 22 |
Expected output would be like quarter wise distinct ID count even if i apply a filter on the [In/out flag] column.
I am getting below output when i apply a filter [In/out flag] = "Out" ( See it's missing some quarters 2010-1 & 2010-4)
In/Out Flag | OUT |
Qtr | Count of Dist ID |
2010-2 | 2 |
2010-3 | 2 |
2011-1 | 2 |
2011-2 | 2 |
2011-3 | 2 |
2011-4 | 2 |
2012-1 | 2 |
2012-2 | 2 |
2012-3 | 2 |
2012-4 | 2 |
but i need the below output
Qtr | Count of Dist ID |
2010-1 | 1 |
2010-2 | 2 |
2010-3 | 2 |
2010-4 | 1 |
2011-1 | 2 |
2011-2 | 2 |
2011-3 | 2 |
2011-4 | 2 |
2012-1 | 2 |
2012-2 | 2 |
2012-3 | 2 |
2012-4 | 2 |
Now i used ALL function
Distinct ID = CALCULATE(DISTINCTCOUNT([ID]),
ALL(
Table[In/Out Flag]
))... but still missing some quarter figures.
Regards,
Peter I
Solved! Go to Solution.
Hi @Peter_Jeyaraj_I ,
It is caused by when you filter the "OUT", the first field quater are also filtered. We can create a spreate calculated table to meet your requirement:
QuarterTable = DISTINCT('Table'[Quarter])
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Peter_Jeyaraj_I ,
It is caused by when you filter the "OUT", the first field quater are also filtered. We can create a spreate calculated table to meet your requirement:
QuarterTable = DISTINCT('Table'[Quarter])
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lid-msft ,
I tried the same las time but didn't work for some reason. Now, it's working fine.
Thanks & Regards,
Peter I
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |