Power BI - filter expression if salesorder doesnt exist

[rsanyoto]

-Hi guys,

I have the following model:

The relationships (based on the 4 numbers in the image above):

Fact_ProductionOrder on  Fact_ProductionOrderLine : many to many

Fact_ProductionOrder on Fact_SalesInvoiceHeader: many to many

Fact_Salesinvoiceheader on Fact_SalesInvoiceLine: one to many

The current situation:

In the following table you see that there are no salesorder from the first twenty rows.

Some productionorders that have a salesorder number return the right values as shown below:

The productionorder column comes from Fact_ProductionOrder

• The Salesorder comes from Fact_SalesInvoiceHeader
• The Unitprice comes from Fact_SalesInvoiceheader
• Finished quantity comes from fact_productionorderline
• VPO Finished is a dax formule: SUM(Fact_SalesInvoiceLine[Unitprice]) * SUM(Fact_ProductionOrderLine[Finished Quantity])
• VPO Variabel is a dax formule:
  
  VPO Variabel =

  CALCULATE([VPO Gepland],

  FILTER(Fact_ProductionOrderLine,

  Fact_ProductionOrderLine[ProductieStatus] = "released"))

  +

  CALCULATE([VPO Finished],

  FILTER(Fact_ProductionOrderLine,

  Fact_ProductionOrderLine[ProductieStatus] = "finished"))

My question is how can i create a solution that checks if a productionorder does not have a salesorder number don't show a wrong result in unitprice column with value such as 954,473,10. Or if a production order does not have a salesorder then dont show a unitprice value.

Many thanks for your support.

[amitchandak]

@rsanyoto 

Fact_ProductionOrder on Fact_ProductionOrderLine : Should be one to Many. why many to many?

see if this can be avoided

Fact_ProductionOrder on Fact_SalesInvoiceHeader: many to many. have a bridge table

https://www.seerinteractive.com/blog/join-many-many-power-bi/

TO filter some data from other table you can use

https://docs.microsoft.com/en-us/dax/treatas-function

a measure like

calculate(count(Fact_ProductionOrderLine [Id]), filter(Fact_ProductionOrderLine , Fact_ProductionOrderLine [orderid] in values(Fact_SalesInvoiceHeader[Order ID])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.