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

[Anonymous]

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!

[Mikelytics · Accepted solution]

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.

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

LinkedIn

[Anonymous]

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.