Search code examples
powerbidaxpowerbi-desktopcalculated-columnsmeasure

DAX expression for Power BI measure

enter image description hereI am trying to create a measure in POWER BI, its to calculate the total revenue for a time period but it has a lot of conditions to it. i.e.

Total Revenue with following conditions Shipping type = "Incoming"

Status = Invoiced,Shipped,Received

if site = 29104 exclude product = S-ExtFreight,S-ProductInDelivery,S-WeighOnly

if site = 33103 exclude product = S-ExtFreight,S-WeighOnly

if site = 23103 exclude product = S-ExtFreight,SAxleWeigh

I tried to use the below expression but its not throwing me any values when I plot it on a graph.

Measure 2 = CALCULATE( sum('Fact table'[Amount]), 'Fact table'[Site Code] = 29104 & 'Fact table'[Item Number] in {"S-ExtFreight","S-ProductInDelivery","S-WeighOnly"}, 'Fact table'[Site Code] = 33103 & 'Fact table'[Item Number] in {"S-ExtFreight","S-WeighOnly"}, 'Fact table'[Site Code] = 23103 & 'Fact table'[Item Number] in {"S-ExtFreight","S-AxleWeigh"})

Thanks Ashok for the update. I get this visual error once I bring it in.

Solution

  • You have to use NOT([Column Name]) in to exclude products. Also you need to use && for AND condition.

    Measure 2 = CALCULATE( sum('Fact table'[Amount]), 'Fact table'[Site Code] = 29104 && NOT('Fact table'[Item Number])  in {"S-ExtFreight","S-ProductInDelivery","S-WeighOnly"}, 'Fact table'[Site Code] = 33103 && NOT('Fact table'[Item Number]) in {"S-ExtFreight","S-WeighOnly"}, 'Fact table'[Site Code] = 23103 && NOT('Fact table'[Item Number]) in {"S-ExtFreight","S-AxleWeigh"})