DISTINCT COUNT with CROSSJOIN

[Anonymous]

Hello, 

I don't have much knowledge of DAX, I made a query with CROSSJOIN, except that as there are duplicate values in the output count, I would like to have unique values. Here is my query:

var NumberAccountsWithAppointements=

FILTER(CROSSJOIN(

                  accounts,SELECTCOLUMNS(appointements,"appointement_account_id",appointments[_regardingobjectid_value])

          ), [accountid]=[appointement_account_id] 

)  RETURN COUNTROWS(NumberAccountsWithAppointements)

As there are multiples rows in appointements table, an account is counted 2 times. 

How can I change this request so that I have distinct count for accounts. 

I Need assistance ASAP.

Thank you. 

[bcdobbs · Accepted solution]

Hi,
Have a look at this Example

Think you were over complicating it with cross join etc rather than using the exisitng relationships.

Using your data...

Customers With An Appointment = 
    DISTINCTCOUNT ( Appointment[CustomerId] )

[smpa01]

@Anonymous  provide sample data/sample pbix

[Anonymous]

@smpa01  I make it very simple so that you get my issue.  This is the result I want. I need a DAX request for that

[Anonymous]

@amitchandak Here is the full request in DAX : 

_NumberAppointementsForUser =

var filteredAccounts = FILTER(accounts, Query[AccountOwnerIdCheck]=1 && accounts[statecode]=0 && accounts[ed_typedefamille]=1)

var filteredAppointements = FILTER(appointments,Query[AppointementOwnerIdCheck]=1)

var nbrdv=

FILTER(CROSSJOIN(filteredAccounts,SELECTCOLUMNS(filteredAppointements,"appointement_account_id",appointments[_regardingobjectid_value])), [accountid]=[appointement_account_id])

RETURN IF(COUNTROWS(nbrdv)=0,CONCATENATE("# ",0), CONCATENATE("# ",COUNTROWS(nbrdv)))

[amitchandak]

@Anonymous ,If this does not help
Can you share sample data and sample output in table format?

[Anonymous]

@amitchandak See what I want to do. Can I have the DAX request for this case please

[bcdobbs · Accepted solution]

Hi,
Have a look at this Example

Think you were over complicating it with cross join etc rather than using the exisitng relationships.

Using your data...

Customers With An Appointment = 
    DISTINCTCOUNT ( Appointment[CustomerId] )

[Anonymous · Accepted solution]

Thank you very much for your held. Indeed DISTINCTCOUNT solve the issue. 

As well I found that I could have juste add a SUMMARIZE in the COUNTROWS : 

RETURN COUNTROWS(SUMMARIZE(NumberAccountsWithAppointements,[accountid])). 

Thanks very much for your help