Power BI - convert Calculated Column into Measure

[Tim_BE]

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

[AlB · Accepted 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