Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Power BI - filter expression if salesorder doesnt exist

-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.

 

1 Reply

  • Anonymous 

    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.