cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sai_Kumar
Frequent Visitor

Hello everyone, I need a help regarding Query language in Power Bi

I had an Open order sales column, Source column, Document date column and Document_due date column in my table(Source column and Document date column append from the other table)

"This was my Formula for Open Order Sale Column"
Open Order Sales = if [Source] = "Actuals" and [ObjType] = "17" and [LineStatus] = "O" then [LineTotal] else 0

The problem was - The Open order sales column is generating values based on Document date(But we need the open order sales to generate regarding Document_due date)

Is there any Function in the query language to tell the open order sales column to take the given column dates?

Thanks to everyone for participating, and I appreciate any help you guys can provide.

1 ACCEPTED SOLUTION

I assume you are just adding the [DocDueDate] and the [Open order sales] fields to a visual and they are not working as expected. This makes perfect sense since the relationship between these 2 columns is not active. To make it work, you should keep the relationships as is but use a measure for the calculation. Assuming you need to calculate the sum of the [Open order sales], you can create and then use the below measure in your visuals:

[SumOpenOrderSales] = CALCULATE(SUM([Open order sales]),USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@Sai_Kumar 

I guess you don't need to append. If you just create a relationship between the Document_due date(Table1) and the Document date(Table2), you should able to use the Document date to filter the measure. The relationship will be CalendarTable <=> Table1 <=>Table2

 

For relationship problems, it can be complicated since we don't have your full model. If possible, you can create a sample pbix with only tables and some relevant columns and removing sensitive data.

 

 

Best Regards

Paul Zheng _ Community Support Team

I tried the way as you said- But really, it doesn't affect anything(Same as before)

 

Sai_Kumar
Frequent Visitor

Hi gdarakji,

Thanks for looking into the issue - Open Order Sales was a measure.


Do you have any date dimension tables related to your fact table? If so, then you need to relate the date table to the Document_due date and use the USERELATIONSHIP function to perform the calculation. If not, then could it be that you are adding the Document date instead of the Document_Due date to the visual?

Yeah, I had a Date table that was Currently related to the Document date.

And, If I make the relationship inactive and If I make the relation active between Document Due Date and Date table then the Budget and Sales are not working (Open Order sales are working great). OPOS.PNG

 

I assume you are just adding the [DocDueDate] and the [Open order sales] fields to a visual and they are not working as expected. This makes perfect sense since the relationship between these 2 columns is not active. To make it work, you should keep the relationships as is but use a measure for the calculation. Assuming you need to calculate the sum of the [Open order sales], you can create and then use the below measure in your visuals:

[SumOpenOrderSales] = CALCULATE(SUM([Open order sales]),USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

View solution in original post

Yoo, That was right! - Thank you gdarakji. 
Really appreciated.

gdarakji
Resolver III
Resolver III

We need more details to be able to assist. Is the [Open Order Sales] field a column in a table or a measure? If it is a column, then please clarify how is it possible for the any date column to impact its value since neither [document date] nor [document_due date] are part of the equation.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors