Forum Discussion

Tim_BE's avatar
Tim_BE
New Member
5 years ago
Solved

Power BI - convert Calculated Column into Measure

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

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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