IF (DAX)

[syasmin25]

Hello, 

I am very much new to DAX calculations and was wondering if you guys could help me out with this. I am attaching an example of what I am trying to replicate in Power BI here. In the attachments, I have a table with the price sold for products in one sheet (meaning a field in PowerBI) and the price that was estimated for sales in another table. The column "Price_to_be_considered" (in table1) shows that if the price at which the product has been sold is less than the price at which it was estimated to be sold then we will take the lower one. However, if not then we the estimated price. I have been trying to implement this into Power BI between two tables and my DAX if statement is not working. 

Any assistance appreciated.

[v-yiruan-msft]

Hi @syasmin25,

First, please make sure there is one relationship be created with field “ID” between your sales table and estimate table. Then create one calculated column on estimated table as below:

1. Create relationship between 2 tables   

2. Create one calculated column with formula:

Price_to_be_considered =

var a=CALCULATE(min('Product Sold'[Price_Sold]),filter('Product Sold','Product Sold'[ID]='Estimated sales'[ID]))

var b=if(a<'Estimated sales'[Estimated Sales Price],a,'Estimated sales'[Estimated Sales Price]) return b

Best Regards

Rena

[syasmin25]

Thank you so much for your help. I apologize for the inconvenience but I forgot to mention that the "Estimated Sales Price" is a measure which I have turned into the column in Power BI. However, even then it does not seem to work.

[v-yiruan-msft]

Hi @syasmin25,

Could you please provide your table structures and the formula of measure "Estimated Sales Price" in order to find the cause of problem? It is better if you can provide your PBIX file (exclude sensitive data). Thank you.

Best Regards

Rena

[syasmin25]

Table_1

Table_2

I created a measure that shows the Quantity sold:

And then I added a Quantity Column for inventory:

Now if I want to see if Quantity_Sold(

COUNTA(Table_2[Product])) is Greater than Quantity (Column from Table_1), then put yes else no. And that is where I am having difficulty since one is a measure and the other one is just a column. 

[v-yiruan-msft · Accepted solution]

Hi @syasmin25,

You can create one measure as below:

salaryR = var a=max('Table_2'[ID])

var b=CALCULATE(max('Table_1'[Quantity]),'Table_1'[ID]=a)

return if([Quantity_Sold]>b,"Yes","No")

Best Regards

Rena

[syasmin25]

Thank you so much. It works!

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[amitchandak]

Assumed first table as price second as estimated

Create a new column in price

t_estimated1 = 
var _Eprice =minx(filter(estimated,price[id]=estimated[id]),estimated[estimated sales price])
return
if(price[price sold] < _Eprice,price[price sold],_Eprice)

Change minx to maxx or < to > as per need