Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I am working with data visualization and trying not to count duplicates and not seeing how to accomplish, the objective is to count only the first ocurrence, using periods of dates, years months etc.
Below is some made up data that should help explain issue:
QUERY(TABLE)
DATE | ID |
01-03-2019 | A |
01-03-2019 | B |
01-03-2019 | C |
02-03-2019 | A |
02-03-2019 | B |
03-03-2019 | D |
03-03-2019 | E |
04-03-2019 | A |
04-03-2019 | C |
04-03-2019 | F |
objective:
ID | 01-03-2019 | 02-03-2019 | 03-03-2019 | 04-03-2019 | TOTAL |
A | 1 | 0 | 0 | 1 | |
B | 1 | 0 | 0 | 1 | |
C | 1 | 0 | 1 | ||
D | 1 | 1 | |||
E | 1 | 1 | |||
F | 1 | 1 | |||
TOTAL | 3 | 0 | 2 | 1 | 6 |
removing the filter ID should also show:
01-03-2019 | 02-03-2019 | 03-03-2019 | 04-03-2019 | TOTAL | |
TOTAL | 3 | 0 | 2 | 1 | 6 |
currently I can not remove the duplicate values,
Measure:=CALCULATE(
COUNTROWS(Query),
FILTER(ALLSELECTED(Query),
COUNTROWS((FILTER(Query, Query[Id]=EARLIER(Query[Id]) && Query[date]>=EARLIER(Query[date]))))
))
ID | 01-03-2019 | 02-03-2019 | 03-03-2019 | 04-03-2019 | TOTAL |
A | 1 | 2 | 3 | 1 | |
B | 1 | 2 | 1 | ||
C | 1 | 2 | 1 | ||
D | 1 | 1 | |||
E | 1 | 1 | |||
F | 1 | 1 | |||
TOTAL | 3 | 0 | 2 | 1 | 6 |
currently I can't remove the duplicate values, but if you can identify where they are, I do not know how to omit these values. I emphasize that I can not occupy calculated columns is too much information and applying only the filters could reduce the processing times in Analysis Services 2014.
Thank You
Solved! Go to Solution.
hi, @jarenasv
You could use this formula to add a measure
result = CALCULATE(COUNTROWS(Query),FILTER(Query,Query[DATE]=CALCULATE(MIN(Query[DATE]),ALLEXCEPT(Query,Query[ID]))))+0
Result:
Best Regards,
Lin
hi, @jarenasv
You could use this formula to add a measure
result = CALCULATE(COUNTROWS(Query),FILTER(Query,Query[DATE]=CALCULATE(MIN(Query[DATE]),ALLEXCEPT(Query,Query[ID]))))+0
Result:
Best Regards,
Lin
Hi, @v-lili6-msft first of all, thanks for your fast and great answer.
if I duplicate the same information for another month(February).
filter date all( february and march), It Works great.
01-02-2019 | 02-02-2019 | 03-02-2019 | 04-02-2019 | 01-03-2019 | 02-03-2019 | 03-03-2019 | 04-03-2019 | Total general | |
A | 1 | 0 | 0 | 0 | 0 | 0 | 1 | ||
B | 1 | 0 | 0 | 0 | 1 | ||||
C | 1 | 0 | 0 | 0 | 1 | ||||
D | 1 | 0 | 1 | ||||||
E | 1 | 0 | 1 | ||||||
F | 1 | 0 | 1 | ||||||
Total general | 3 | 0 | 2 | 1 | 0 | 0 | 0 | 0 | 6 |
but until I apply a filter to Date column or between dates.
example, filter date(march)
I tried using the allselected function but it don't work
01-03-2019 | 02-03-2019 | 03-03-2019 | 04-03-2019 | Total general | |
A | 0 | 0 | 0 | 0 | |
B | 0 | 0 | 0 | ||
C | 0 | 0 | 0 | ||
D | 0 | 0 | |||
E | 0 | 0 | |||
F | 0 | 0 | |||
Total general | 0 | 0 | 0 | 0 | 0 |
I’d appreciate your help, and tell me how to consider the date filter context.
Thanks in advance & best regards
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |