Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
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
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(
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
Thank you so much. It works!
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...
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |