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.
Hi all,
I've two tables:
1. SalesPrices, with multiple [Item_ID] and [Sales_Price] and connected [account_ID]. When the field [Account_ID] is blank / empty then this means that this is de base sales price.
2. SalesVolumePrices, with [Item_ID]. In this table I would like to add the column [Sales_Price] from table SalesPrices in which case the field [Account_ID] is blank / empty.
I've tried verything with LOOKUPVALUE and so on, but nothing works and I'm lost. Someone who can help?
Solved! Go to Solution.
Hey @AltusTellus ,
does the table SalesVolumePrices already exist?
Or do you want to creat it with DAX?
If you have to create it, why don't you copy the original table in Power Qery and remove all rows where Account_ID is not empty?
Then you have a prepared table directly from Power Query.
Best regards
Denis
Table [SalesPrices] Result should be Table [SalesVolumePrices]
Item_ID Sales_Price Account_ID Item_ID Standard_Sales_Price
100001 2,20 95642 100001 1,75
100001 1,75
100001 1,44 98677
Hey @AltusTellus ,
does the table SalesVolumePrices already exist?
Or do you want to creat it with DAX?
If you have to create it, why don't you copy the original table in Power Qery and remove all rows where Account_ID is not empty?
Then you have a prepared table directly from Power Query.
Best regards
Denis
Hello @AltusTellus ,
I guess you want the base sales price as a calculated column in the table SalesVolumePrices.
Try if the following measure works for you as a calculated column:
base sales price =
VAR rowItemID = SalesVolumePrices[Item_ID]
VAR result =
CALCULATE(
MAX( SalesPrices[Sales_Price] ),
SalesPrices[Item_ID] = rowItemID,
ISBLANK( SalesPrices[account_ID] )
)
RETURN
result
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Hi @selimovd the formula giver no error(s), but the returned value is blank. Any ideas?
If the value is an empty sting and not really blank this could be an issue.
Try the following version:
base sales price =
VAR rowItemID = SalesVolumePrices[Item_ID]
VAR result =
CALCULATE(
MAX( SalesPrices[Sales_Price] ),
SalesPrices[Item_ID] = rowItemID,
SalesPrices[account_ID] = ""
)
RETURN
result
Thnx for your reply, but this gives me the same results: all blanks. To be sure, here is my example:
Table [SalesPrices]
Item_ID Sales_Price Account_ID
100001 1,50 76548
100001 1,25
100001 1,10 74888
Table [SalesVolumePrices]
Item_ID Standard_Sales_price
100001 1,25
So, the result must be 1,25 because that's the value without an Account_ID in the row.
Do you have more suggestions?
@AltusTellus , can you share some sample data. refer my two videos. It has 4 ways
https://www.youtube.com/watch?v=czNHt7UXIe8
https://www.youtube.com/watch?v=Wu1mWxR23jU
you may have add additional filter
&& isblank( [Account_ID])
or
&& not isblank( [Account_ID])
Hi @amitchandak The two tables don't have a relationship, do you have another solution?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |