Forum Discussion
USERELATIONSHIP with DATESINPERIOD in Calculated Column
Morning all,
Firstly, I have scoured the online forums trying to find the same issue, and while I have found similar ones (2 examples below), none cater for my specific issue so far, which is the inclusion of DATESINPERIOD.
Solved: Calculated Columns UseRelationship Different Dates... - Microsoft Power BI Community
Solved: USERELATIONSHIP Strange behavior !!! - Microsoft Power BI Community
Schema
File Link
https://1drv.ms/u/s!AvW1rD07hRaW3V7Os50SVH4F-j4D?e=8ZJtrF
Problem
I have a Alerts fact table with two dates linked to the Date table. I need to apply a calculated columns to the Alert table to chart the number of Alerts per month according to Onboard Date (inactive relationship), such that the chart will show 6 months of data at a time (unless sliced) and still accurately interact with other visuals.
I have applied the formula successfully on the Case Table using the active relationship of Case Creation Date:
Created Count =
CALCULATE (
COUNTROWS (Cases ),
DATESINPERIOD ('Date Table'[Date], MAX ('Date Table'[Date]), -6, MONTH))
However, when I try to replicate this using the inactive relationship of Onboard Date on the Alerts table as follows, it returns the same values as though I were counting on the active relationship of Case Creation Date.
Onboard Count =
CALCULATE (
COUNTROWS (Alerts),
USERELATIONSHIP (Alert [Onboard Date], 'Date Table'[Date]),
DATESINPERIOD ('Date Table'[Date], MAX ('Date Table'[Date]), -6, MONTH))
Related to this seems to be an issue with not being able to apply two calculated columns with the same formula in the same table i.e. if I wanted to count Alerts on both Created Date and Onboard date.
How can this formula be modified to replicate the output of the first example?
2 Replies
Merc100 , Use relationship need to be used in measure. The second one need to be a measure
Try like
CALCULATE (CALCULATE (
COUNTROWS (Alerts),
USERELATIONSHIP (Alert [Onboard Date], 'Date Table'[Date]) ) ,
DATESINPERIOD ('Date Table'[Date], MAX ('Date Table'[Date]), -6, MONTH))
amitchandak - Thanks for taking a look. I tried that but the result was that each month total was added to that of the previous month, essentially as a rolling total, whereas I want to show the last 6 months in their own right. If you look at the file I linked, you can see the formula (without USERELATIONSHIP) worked fine in the Case Table, so I need to find a way to activate the inactive relationship in the Alert Table.
