Forum Discussion
Distinctcount crashing my PC
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 🙂
Super Userthe 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
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.
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 🙂