Return value from another table (no relationship) with filter value is blank

[AltusTellus]

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?

[AltusTellus]

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

[selimovd · Accepted 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

[selimovd]

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

[AltusTellus]

Hi @selimovd the formula giver no error(s), but the returned value is blank. Any ideas?

[selimovd]

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

[AltusTellus]

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]

@selimovd do you have another solution for me? In advance many thnx!

[amitchandak]

@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])

[AltusTellus]

Hi @amitchandak The two tables don't have a relationship, do you have another solution?