Forum Discussion
Distinctcount crashing my PC
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])
)
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 🙂
5 Replies
- amitchandak
Super User
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.
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])
)
And I need it as calculated column so that I can filter customers by this average afterwards
