Var to filter table return to excute distinctcount

[Anonymous]

I have a Data table with data rows that denote each record ( with unique ID ) by program , by location by date.
ID. Program Location Date
1. A. XXX. 1.1.2019
2. A YYY. 2.1.2019
3. B XXX. 3.1.2019
1. A. XXX. 3.1.2019
I’ve also create Dim table for Program , Location & Date respectively & all have relationship with Data table.
Intend to use dax 1) with use of Var to filter the record By ID for only a program ,
and 2) to return a Distinct count to sieve out by ID for a specific date range as new unique record .
Below Dax work for part 2 ( can pick out new unique record ）but cannot incorporate part 1.
New unique =
Var Attendee = values(‘Data [ID])
return COUNTROWS(filter(Attendee,CALCULATE ( COUNTROWS(‘Data, filter( ALLSELECTED( ‘Dim Dates[dates]),’Dim dates[dates]< min (‘Dim Dates[ dates] )))= 0 ))

[v-yuta-msft]

@Anonymous ,

I’ve also create Dim table for Program , Location & Date respectively & all have relationship with Data table.
Intend to use dax 1) with use of Var to filter the record By ID for only a program ,
and 2) to return a Distinct count to sieve out by ID for a specific date range as new unique record .
Below Dax work for part 2 ( can pick out new unique record ）but cannot incorporate part 1.
New unique =
Var Attendee = values(‘Data [ID])
return COUNTROWS(filter(Attendee,CALCULATE ( COUNTROWS(‘Data, filter( ALLSELECTED( ‘Dim Dates[dates]),’Dim dates[dates]< min (‘Dim Dates[ dates] )))= 0 ))

---

Could you please also share your sample dim table and expected result?

Regards,

Jimmy Tao

[Anonymous]

Jimmy
Sample of the Dim table for Program
Program. Program category
A. Categ 1
B. Categ 1
C. Categ 2
D Categ 2

DAX is to sieve through the data tables record, and use the date field to find if specific record (ID) is 1st time - eg a new customer .

As 2nd explained in earlier post , I believe 2nd part of the dax ( using filter of all selected date) is able to filter such records and do a record count.

My issue is that I also like to sieve through the data tables only for specific program ( eg static program A ) . I can’t find a way to add in this filter of another column

[CheenuSing]

Hi @Anonymous ,

Can you uplload sampledata,pbix and output expected to Google / OneDrive and share the link here to understand better and formulate a solution.

Cheers

CheenuSing

[Anonymous]

CheenuSing
Thanks for response but I can’t share pbi file. I have added more information on my issue as rely to Jimmy . Please see if that help to understand my issue