Forum Discussion
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
- amitchandak
Super User
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.