Distinctcount crashing my PC

[broodstar]

Dear Dax users I need help or I go crazy,

I have two tables in data model - Data, Customers 

Data
Customer	Month	Product	KR
1019593	2020/06	Product A	10
1019593	2020/06	PRoduct B	2
...	...	...	...

Customers
Customer	Selected month	Countrows	Distinct count month
1019593	2020/06	2	crashing PC
...	...	...	...

I want to know number of months in which any product was sold. I cannot use countrows, becuase it returns 2 as there are two products sold in one month.

= CALCULATE

(

  Countrows(Data);

   FILTER(Data; Data[Customer] = Customers[Customer]);

   FILTER(Data; Data[Month] = Customers[Selected month])

)

I guess I need to use distinct count, but if I put the formula below, the pc crashes. Do you where could be the problem? Please?

= CALCULATE

(

  DISTINCTCOUNT(Data[Month]);

   FILTER(Data; Data[Customer] = Customers[Customer]);

   FILTER(Data; Data[Month] = Customers[Selected month])

)

[amitchandak]

@broodstar ,

the calculation seems like a candidate for the measure but seem like you have created a measure.

Why the customer table has a month?

In case the join is month and customer than create a concatenated column and join both tables

Key = [Customer] & "-" & [Month] //Something like this in both tables

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[broodstar]

thank you @amitchandak                      for quick response

Customer has month, because it is a calculated column as first month of purchase of selected product i needed. Lets say product A.

Then I want to get average monthly sale of all products for this customer from that month. I though I would just use sum (that is working) and divide it by unique number of months (formula below, not working).

Do you have any idea mayebe how to get the average monthly sales from selected dat for selected customer differently?

= CALCULATE

(

  DISTINCTCOUNT(Data[Month]);

   FILTER(Data; Data[Customer] = Customers[Customer]);

   FILTER(Data; Data[Month] >= Customers[Selected month])

)

[broodstar]

And I need it as calculated column so that I can filter customers by this average afterwards

[v-jayw-msft]

Hi @broodstar ,

Check this formula.

Column = 
CALCULATE ( SUM ( 'Table'[KR] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) )
    / CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[broodstar · Accepted solution]

I have finally substituted

= CALCULATE

(

  DISTINCTCOUNT(Data[Month]);

   FILTER(Data; Data[Customer] = Customers[Customer]);

   FILTER(Data; Data[Month] = Customers[Selected month])

)

by 

= CALCULATE

(

  DISTINCTCOUNT(Data[Month]);

   Data[Customer] = EARLIER(Customers[Customer]));

   Data[Month] = EARLIER(Customers[Selected month]))

)

and it works, I do not know what kind of sorcery is this, but thank you to everyone for your help 🙂