topic How to distinct count an column only on the first date in DAX? in DAX Commands and Tips

How to distinct count an column only on the first date in DAX?

Anonymous — Wed, 14 Dec 2022 12:10:07 GMT

I have a Table ('Table') with two Projects (ABC-10 and ABC-11) where an Project can occur over multiple dates due to having different Sub Project states and Companies.

Project	Sub Project	Company	Date
ABC-10	1	A	30/09/2021
ABC-10	2	A	30/09/2021
ABC-10	1	B	30/06/2022
ABC-10	1	C	30/09/2021
ABC-10	1	D	30/09/2021
ABC-10	2	D	30/08/2021
ABC-11	1	A	30/09/2021
ABC-11	2	A	30/09/2021
ABC-11	1	B	30/06/2022
ABC-11	1	C	30/09/2021
ABC-11	1	D	30/09/2021
ABC-11	2	D	30/01/2020

What I need:

- I would like to create one measure ([MyMeasure]) to distinct count the Project by the first Date at wich project occurred.

- I pretend that [MyMeasure] provides me the same approach based on the companies I select.

- This measure is needed for Bars Charts and table visuals.

This is the expected result if I select all companies:

Date	MyMeasure
30/01/2020	1
30/08/2021	1

This is the expected result if I select the companies A and B :

Date	MyMeasure
30/06/2022	2

I already create one measure wich provides me the right value if I don't select any company.

MyMeasure = VAR t = CALCULATETABLE( VALUES('Table'[Project]) ,FILTER( 'table' ,VAR dateInRow=[Date] RETURN dateInRow=CALCULATE( min('Table'[Date]) ,ALLEXCEPT('Table','Table'[Project])))) var result = COUNTROWS(t) return result

The problem is when I select one or more companies (A and B in this example) the maesure doesn't calculate the right value:

How can I solve this?

Thank you!

Re: How to distinct count an column only on the first date in DAX?

Mikelytics — Wed, 14 Dec 2022 15:42:52 GMT

Hi Anonymous

I produced the following measure and I would say it works. What I do not get is why you expect 30/06/2022 when choosing A and B. WOuldnt it be 30/09/2021?

Here my measure: SampleM99106 is the name of your table

MyMeasure = var __FirstDateOfProject = CALCULATETABLE( SUMMARIZE(SampleM99106,[Project],"FirstDate",FIRSTDATE(SampleM99106[Date])), ALLEXCEPT(SampleM99106,SampleM99106[Project],SampleM99106[Company]) ) var __SelectedDate = SELECTEDVALUE(SampleM99106[Date]) var __Result = COUNTROWS( FILTER(__FirstDateOfProject,[FirstDate]=__SelectedDate) ) Return __Result

Best regards

Michael

-----------------------------------------------------

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

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

Re: How to distinct count an column only on the first date in DAX?

Anonymous — Wed, 14 Dec 2022 16:48:29 GMT

Hey,

This approach works! Thank you!

Yes, sorry, my bad. The correct return for A and B companies is two projects on 30/09/2021.