March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
If I have two simple tables:
Calendar:
Date Work Day
20180101 0
20180102 0
20180103 1
20180104 0
20180105 1
20180106 0
20180107 1
Revenue:
DateRevenue Rev
20180101 100
20180103 200
20180104 300
20180104 400
20180107 500
20180107 600
These two tables are not linked in the model. I need to create a DAX meaure. Work Day would be a slicer - when the user selects "1" all Revenue where "DateRevenue" equals the "Date" field would be summed. In this example the total would be 1300 which is the total of dates 20180107 and 20180103 (the values that have a "1" in the calendar table.)
Thanks,
Z
Solved! Go to Solution.
Yep I know the pain,
You can create any number of relationships between a master calendar and multiple date columns in a fact but only 1 can be "enabled" to prevent a circular reference. You may have seen this if you try and join the calendar twice to the same fact - one solid line and one dotted line relationship - you can enable/disable any relationship by going to its properties and select "make this relationship active" tickbox
To get around this you can hard code a measure to force the relationship:
heres a toob which probably explains it better than me:
sqlbi have a puzzle around this common scenario:
https://www.sqlbi.com/daxpuzzle/userelationship/
Something like this may work for you:
Measure = CALCULATE ( SUM ( Revenue[Rev] ), INTERSECT ( VALUES ( Revenue[DateRevenue] ), VALUES ( Calendar[Date] ) ) )
Thanks
Another option is to use SUMX. Add a table or matrix with Calendar[Date] as rows and then the below measures. Not sure if you actually don't want to count Rev at all unless it's on a work day or if you just want to be able to filter by workday, so I provided two:
WorkFilter = SUMX('Calendar',0+CALCULATE(SUM(Revenue[Rev]),FILTER(Revenue,Revenue[dateRevenue]='Calendar'[Date])))
WorkOnly = SUMX('Calendar',0+CALCULATE(SUM(Revenue[Rev]),FILTER(Revenue,Revenue[dateRevenue]='Calendar'[Date] && 'Calendar'[Work Day]=1)))
If you would prefer there to be blanks instead of 0's for days with no revenue, just remove the "0+" in front of the CALCULATE.
Thanks - new to Power Bi - helps alot.
Probably the simplest solution is to created a disabled relationship between the Calendar[Date] and Revenue [DateRevenue]
Then build a measure like so:
Revenue = CALCULATE([Total Revenue],USERELATIONSHIP('Date'[Date],Revenue[DateRevenue]))
Thanks for the response.
The problem is we have a ton of date fields in our model so it isn't possible to create a relationship to the calendar for each date field in each dimensions and fact table.
Can you explain "disabled relationship"? Might be what I need...
Z
Yep I know the pain,
You can create any number of relationships between a master calendar and multiple date columns in a fact but only 1 can be "enabled" to prevent a circular reference. You may have seen this if you try and join the calendar twice to the same fact - one solid line and one dotted line relationship - you can enable/disable any relationship by going to its properties and select "make this relationship active" tickbox
To get around this you can hard code a measure to force the relationship:
heres a toob which probably explains it better than me:
sqlbi have a puzzle around this common scenario:
https://www.sqlbi.com/daxpuzzle/userelationship/
Very helpful! Thanks.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |