Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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]))

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors