Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
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])
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |