Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Use Calendar Current Month to filter on non-related table

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

 

1 ACCEPTED 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/

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

8 REPLIES 8
mattbrice
Solution Sage
Solution Sage

Something like this may work for you:

 

Measure =
CALCULATE (
    SUM ( Revenue[Rev] ),
    INTERSECT ( VALUES ( Revenue[DateRevenue] ), VALUES ( Calendar[Date] ) )
)
Anonymous
Not applicable

Thanks

MarkLaf
Solution Sage
Solution Sage

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)))

notfiltered.PNGfiltered.PNG

 

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.

Anonymous
Not applicable

Thanks - new to Power Bi - helps alot.

dearwatson
Responsive Resident
Responsive Resident

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]))

Capture.PNG

 

Capture2.PNG

 

 

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Anonymous
Not applicable

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/

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Anonymous
Not applicable

Very helpful! Thanks.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.