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,
Hope you can help me out converting a calculated column into a measure. Searched in the forum, but couldn't find a solution that worked well.
I created a calculated column in a data table called “Calendar”. It counts the open cases we had on each calendar date:
OpenCases = CALCULATE(DISTINCTCOUNT('SFDC CASE LIST'[Case Number]), FILTER('SFDC CASE LIST', 'SFDC CASE LIST'[Opened Date] < Calendar[Date]), FILTER('SFDC CASE LIST', 'SFDC CASE LIST'[Closed Date] > Calendar[Date] || 'SFDC CASE LIST'[Closed Date] = BLANK() ))
The calculated column works well. But when implementing in a visual, report filters are not applied on a calculated column. So tried to change this into a measure but failed, error message “A single value for column Date in table Calendar cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min… “
Measure = CALCULATE(DISTINCTCOUNT('SFDC CASE LIST'[Case Number]), FILTER('SFDC CASE LIST', 'SFDC CASE LIST'[Opened Date] < Calendar[Date]), FILTER('SFDC CASE LIST', 'SFDC CASE LIST'[Closed Date] > Calendar[Date] || 'SFDC CASE LIST'[Closed Date] = BLANK() ))
The idea is to create a visual (e.g. line chart) in Power BI and calculating the Measure for each date present in table “Calendar”. This date would be the axis of my visual.
Kind regards,
Tim
Solved! Go to Solution.
Hi @Tim_BE
This assumes no relationship between your Calendar and fact table:
OpenCases M =
VAR currentDate_ =
MAX ( Calendar[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'SFDC CASE LIST'[Case Number] ),
'SFDC CASE LIST'[Opened Date] < currentDate_,
FILTER (
ALL ( 'SFDC CASE LIST'[Closed Date] ),
'SFDC CASE LIST'[Closed Date] > currentDate_
|| 'SFDC CASE LIST'[Closed Date] = BLANK ()
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Tim_BE
This assumes no relationship between your Calendar and fact table:
OpenCases M =
VAR currentDate_ =
MAX ( Calendar[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'SFDC CASE LIST'[Case Number] ),
'SFDC CASE LIST'[Opened Date] < currentDate_,
FILTER (
ALL ( 'SFDC CASE LIST'[Closed Date] ),
'SFDC CASE LIST'[Closed Date] > currentDate_
|| 'SFDC CASE LIST'[Closed Date] = BLANK ()
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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.