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.
I have two queries:
Budgets - contains columns for store, sales_amount, and a field with a concatenation of year and week (ex. 2018;1) so there is a single row in this query for each combination of store and week.
Dates - contains a row for every relevant date, along with a variety of information about that date, including the same concatenation of year and week, as well as a field that indicates how long ago that date was from today (in weeks)
The tables are joined to each other on the year/week field. It is a many to many relationship because there are 7 dates for each week and there are multiple locations for each week.
I created a measure that is the sum of sales_amount. I then created a card to show that measure filtered to show only the date rows where the "weeks ago" field was 1, I was concerned about how I going to get it to sum just a single value per week, but was surprised to find out that it worked immediately. Obviously, in SQL this join would have produced 7 copies of each budget row, one for each date in that week. But that's not happening.
Is this the expected behavior (I'm assuming it is, but try to make sure I understand the boundaries of how it works)? It appears that only distinct rows are being considered by the measure, where distinct is determined using only the fields that are actually used by the visualization (including its filters, page filters, etc.) Is that the right way to think about what it's doing?
Solved! Go to Solution.
Hi @dovrosenberg,
Since you established a relationship based on Year-Week, the row context the measure is calculated based on should be Year-Week. So, it returned weekly total rather than daily. Here are some links written about row context:
Regards,
Yuliana Gu
Hi @dovrosenberg,
Since you established a relationship based on Year-Week, the row context the measure is calculated based on should be Year-Week. So, it returned weekly total rather than daily. Here are some links written about row context:
Regards,
Yuliana Gu
Thanks. Those links were super helpful.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |