Forum Discussion
Calculated column taking forever
Hi, i have a copied formula that has been working for all the other Power Bi desktop reports so far. But for a new client that has over 1.2 million rows which i think is the problem here, the following dax query for creating a calculated column just stays on "working from it". Is there any way to modify the query so that it can work out, or tips in general for large tables?
Appreciate the Help!
Distinct Years of Giving = IF(CALCULATE(DISTINCTCOUNT(GIFT_TABLE[Gift Date].[Year]),FILTER(GIFT_TABLE,GIFT_TABLE[donor_id]=DONOR_TABLE[donor_id])) > 0,CALCULATE(DISTINCTCOUNT(GIFT_TABLE[Gift Date].[Year]),FILTER(GIFT_TABLE,GIFT_TABLE[donor_id]=DONOR_TABLE[donor_id])),0)
Hello there Anonymous ! My tip would be to never use calculated columns, specially in Fact tables! You can do the same query in the Query Editor instead of DAX.
Another option is to store the results in a measure, or variable within a measure, and lighten the storage that PBI needs.
In terms of code, you can lighten it with variables like so:
Distinct Years of Giving = VAR _count = CALCULATE ( DISTINCTCOUNT ( GIFT_TABLE[Gift Date].[Year] ), FILTER ( GIFT_TABLE, GIFT_TABLE[donor_id] = DONOR_TABLE[donor_id] ) ) RETURN IF ( _count > 0, _count, 0 )Again, I reinforce that you should do this as a measure and not a calculated column. You can check further documentation on the use cases of both measures and calculated columns in the following link.
Measure vs Calculated Column: The Mysterious Question? Not! - RADACAD
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes
3 Replies
Hi Anonymous ,
You can try below formula:-
Distinct Years of Giving = VAR _value = CALCULATE ( DISTINCTCOUNT ( GIFT_TABLE[Gift Date].[Year] ), FILTER ( GIFT_TABLE, GIFT_TABLE[donor_id] = DONOR_TABLE[donor_id] ) ) RETURN IF ( _value > 0, _value, 0 )Thanks,
Samarth
What table are you adding this "column" to?
Perhaps, in your gift table add a calculated columm
"Donor Year" = [donor_id] &"-"&Text.From([Gift Date].[Year])
Now create a measure
Distinct Years of Giving = DistinctCount([Donor Year])
Hello there Anonymous ! My tip would be to never use calculated columns, specially in Fact tables! You can do the same query in the Query Editor instead of DAX.
Another option is to store the results in a measure, or variable within a measure, and lighten the storage that PBI needs.
In terms of code, you can lighten it with variables like so:
Distinct Years of Giving = VAR _count = CALCULATE ( DISTINCTCOUNT ( GIFT_TABLE[Gift Date].[Year] ), FILTER ( GIFT_TABLE, GIFT_TABLE[donor_id] = DONOR_TABLE[donor_id] ) ) RETURN IF ( _count > 0, _count, 0 )Again, I reinforce that you should do this as a measure and not a calculated column. You can check further documentation on the use cases of both measures and calculated columns in the following link.
Measure vs Calculated Column: The Mysterious Question? Not! - RADACAD
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes
