Calculated column taking forever

[Anonymous]

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)

[goncalogeraldes · Accepted solution]

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

[I_Like_Pi]

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])

[Samarth_18]

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