Hello everyone, I need an help Regarding Power Query/Dax in Power BI

[Sai_Kumar]

Hi Guys, 
Morning!

I am working on Sales data and the data has Open Order Sales by Doc_date and Doc_due_date in the MCS_PowerBI_Fact table.

Requirement: I am looking for an Open Order Sales visual by Doc_due date. - Currently, I am getting open order sales by Doc_date

Measures I am using

The following measure was for currency conversion,

OLD_Open_Order_Sales = SWITCH(

'Currency Selection'[SelectCurrency],

"Local Currency",SUMX(MCS_PowerBI_Fact,MCS_PowerBI_Fact[OpenOrderSalesLocal]),

"Australian Dollar",SUMX(MCS_PowerBI_Fact,MCS_PowerBI_Fact[OpenOrderSalesAUD]))

And By the following measure I am trying to get open order sales by DocDueDate - But, still, it was extracting by Doc date -

OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]),USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

 
***I need a measure to get the open order sales by DocDueDate.*** and if possible, to include both OpenOrderSalesAUD and OpenOrderSalesLocal in a measure.

Coming to relationships between two tables(MCS_PowerBI_Fact[DocDate] and Master calendar table[Date])

And, If I change the relationship to Doc_Date to Doc_Due_Date then the sales figures are effecting(Which was sales figures are getting wrong)

Any help can be appreciated 

[Anonymous]

Sorry--copy/paste error:

OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]), DISTINCT('MCS_POWERBI_FACT'[DocDueDate]), USERELATIONSHIP('MCS_POWERBI_FACT'[DocDueDate],'Master Calendar'[Date]))

[Sai_Kumar]

I tried it - But, the measure output was the same as my last measure as mentioned in the post.

[Sai_Kumar]

If you see the Doc_Date and Doc_Due_Date columns 
The requirement was, we need to extract only Doc_Due_date if Doc_Due_date was in the Current month.

The OLD_Open Order Sales measure was working but giving us all open order Sales - where we need only present and future months open order sales.  

[v-yingjl]

Hi @Sai_Kumar ,

Consider using Crossfilter() to change the cross filter direction or change it manually in the relationship view to check whether it works.

Plesae refer: Bi-directional relationship guidance 

If it still has the same issue, you can consider sharing a simple sample file without any sentive information for further discussion.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Try:

OpenOrderSales = CALCULATE(SUM([OpenOrderSalesAUD]), DISTINCT('MCS_POWERBI_FACT'[DocDueDate]), USERELATIONSHIP('Master Calendar'[Date]))

--Nate

[Sai_Kumar]

Hi Watkinnc,

Thanks for considering!

I tried the way as you said 

The error follows-
Too few arguments were passed to the USERELATIONSHIP function. The minimum argument count for the function is 2.