cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

1 ACCEPTED SOLUTION
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

1 REPLY 1
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors