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
jarenasv
Frequent Visitor

Dax count first occurence by period

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)

DATEID
01-03-2019A
01-03-2019B
01-03-2019C
02-03-2019A
02-03-2019B
03-03-2019D
03-03-2019E
04-03-2019A
04-03-2019C
04-03-2019F

 

objective:

ID01-03-201902-03-201903-03-201904-03-2019TOTAL
A10 01
B10 01
C1  01
D  1 1
E  1 1
F   11
TOTAL30216

 

removing the filter ID should also show:

 01-03-201902-03-201903-03-201904-03-2019TOTAL
TOTAL30216

 

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]))))
))

 

ID01-03-201902-03-201903-03-201904-03-2019TOTAL
A12 31
B12  1
C1  21
D  1 1
E  1 1
F   11
TOTAL30216

 

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

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

10.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
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
v-lili6-msft
Community Support
Community Support

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:

10.JPG

 

Best Regards,

Lin

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

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-201902-02-201903-02-201904-02-201901-03-201902-03-201903-03-201904-03-2019Total general
A10 000 01
B10  00  1
C1  00  01
D  1   0 1
E  1   0 1
F   1   01
Total general302100006

 

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-201902-03-201903-03-201904-03-2019Total general
A00 00
B00  0
C0  00
D  0 0
E  0 0
F   00
Total general00000

 

I’d appreciate your help, and tell me how to consider the date filter context.

 

Thanks in advance & best regards

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.