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 table of purchase transactions of products and dates,
and a table of warehouse entries by dates.
I need to sum of values of purchases just for products with at least one entry on the year with the purchase like on the picture.
Is this the way to do it with DAX?
The link for the sample PBIX:
https://drive.google.com/file/d/1Or0XWXEYVEGYirSQfJUyIGObgw9G3mlm/view?usp=sharing
. attached
Solved! Go to Solution.
Try this measure. I added a relationship between the calendar and entries tables. To confirm the logic: sum the purchase value for each product that has at least one row in the entries table (for the selected year).
Purchases with Entry =
CALCULATE (
SUM ( purchase[value] ),
FILTER ( products, NOT ISEMPTY ( RELATEDTABLE ( entries ) ) )
)
Proud to be a Super User!
Glad to hear the solution works.
If the connection between the purchase and entries tables is PurchaseItemID, the DAX would need to be modified. In a star schema, there aren't relationships between fact tables (the relationships are between dimension and fact tables), which prevents you from using RELATEDTABLE as it exists in the measure.
Proud to be a Super User!
Hi @Ritaf ,
Whether the advice given by @DataInsights has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
Try this measure. I added a relationship between the calendar and entries tables. To confirm the logic: sum the purchase value for each product that has at least one row in the entries table (for the selected year).
Purchases with Entry =
CALCULATE (
SUM ( purchase[value] ),
FILTER ( products, NOT ISEMPTY ( RELATEDTABLE ( entries ) ) )
)
Proud to be a Super User!
Thank you!
This solution is perfect in this context.
One more question:
If the model is built so that there is a connection between purchase transactions and entries by purchaseitemid ( not by-product like in my sample file) is this the way to handle it with DAX or do I need to build this logic in the model itself?
Glad to hear the solution works.
If the connection between the purchase and entries tables is PurchaseItemID, the DAX would need to be modified. In a star schema, there aren't relationships between fact tables (the relationships are between dimension and fact tables), which prevents you from using RELATEDTABLE as it exists in the measure.
Proud to be a Super User!
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 |
---|---|
116 | |
102 | |
78 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |