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 all,
I'm working with facts-table which doesn't have a date column but instead has a calendar year/week column (yyyyww). My date table has this column as well but this results in a many-to-many relationship. I would like to group my date table by week, resulting in only one row per week.
How would I go about this?
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous
Thanks for reaching out to us.
>> I would like to group my date table by week, resulting in only one row per week.
You can try this,
OnlyOneRow_PerWeek =
var _maxPerW=CALCULATE(MAX('date'[Date]),FILTER(ALL('date'),'date'[YW]=EARLIER('date'[YW])))
return IF('date'[Date]=_maxPerW,'date'[YW])
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Thanks for reaching out to us.
>> I would like to group my date table by week, resulting in only one row per week.
You can try this,
OnlyOneRow_PerWeek =
var _maxPerW=CALCULATE(MAX('date'[Date]),FILTER(ALL('date'),'date'[YW]=EARLIER('date'[YW])))
return IF('date'[Date]=_maxPerW,'date'[YW])
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
You could try creating a bridge table like
Year Week Table = ALLNOBLANKROW('Date'[Year Week])
and then creating relationships from the new table to your fact table and your date table. Make the relationship with the date table bidirectional.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |