cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Smeek
Regular Visitor

On Time calculation with comparison between two dates and filter on one date

Dear BI pros,

 

I hope you can help me with the following:

I am trying to count the processing steps that were performed on time for any given period.

But for some reason I am having problems with the DAX function:

 


on time = CALCULATE(DISTINCTCOUNT(Processing Steps[Processing Steps No]),

Processing Steps[Processing Steps Status]="Finished",

USERELATIONSHIP(Processing Steps[Target Enddate],Date[Date]),

Processing Steps[Actual Enddate]<=Processing Steps[Target Enddate]))

 

DAX does not accept the last part: The actuals enddate should be before the Target Enddate.

I receive the error: The expression contains multiple columns, but only a single column can be used in a true/false expression, which serves as a table filter expression.

I tried wrapping the FILTER function around it, but it didn't help.

Here the set up:
Tables:

DIM_Date: active relationship to start date in Processing Steps table and inactive relationship to Target Enddate.

Fact_Processing Steps: Processing Steps Nr, Target Enddate, Status, Actual Enddate, Start Date

Because there were blanks in the Actual Enddate , I filled these with the date 2099.12.31, hoping that would help...

In the end the enduser should be able to chose any date and the visual should show the amount of closed processing steps that had the Target Enddate in the chosen period (thus the inactive relationship), the Status on 'Finished' and the Actual Enddate before or equal to the chosen (Target End-)date.

What am I missing?

Thanks for your help!

Smeek
2 REPLIES 2
Greg_Deckler
Super User
Super User

@Smeek - If this is a measure, you need to wrap column references in an aggregator so maybe try. @ me in replies

 

on time = CALCULATE(DISTINCTCOUNT(Processing Steps[Processing Steps No]),

Processing Steps[Processing Steps Status]="Finished",

USERELATIONSHIP(Processing Steps[Target Enddate],Date[Date]),

MAX(Processing Steps[Actual Enddate])<=MAX(Processing Steps[Target Enddate])))

If not, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi Greg,

 

thanks for your answer.

Yes, it is a measure I am trying to calculate.

I tried wrapping the date comparision with MAX, MIN, Filter...but it doesn't work. The message: Only a column reference is accepted as argument by the function 'MAX'.

 

Both date columns are formated as dates, although the 'Actual Enddate' is a calculated column pulling the dates from another table via RELATED.

I will try and see if I can setup a sample database.

 

Thanks

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors