Get first value from column that occurs in a other column

[Anonymous]

Hi all,

I currently have a measure that summarizes a few rows together. These rows have the same ID, NAME, different DATES and also different PRODUCTS. At the moment, the table can show the ID & NAME paired with the DATE and summarize the VALUES. Here's my previous post for a better understanding: How can I sum all rows that have the same ID and Date? 

As said before, that table also has a PRODUCTS column. Every row has a different PRODUCT. I have a table holding specific PRODUCTS, that can only occur once per DATE/ID&NAME. Now I need a measure, that goes into the PRODUCTS-Table, search if there is any PRODUCTS in any of the selected rows and then give me that PRODUCT. If there's no match, there should be a text like "No product found".

Here's a example:

First we have the PRODUCTS-Table:

Then we have the normal table holding the data:

And here it is how it should look in the end:

For simplicity I did not inclue VALUE and STATUS.

We can take the two blue rows for example (note that there can and will be more than 2 rows for the same DATE/ID&NAME). The measure now takes the two PRODUCTS, sees if any of them are in the PRODUCTS-Table and puts the one that is in said table as value.

I hope it is clear what I want to achive. If not, I can update the post or comment in the thread.

Thanks for the help.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can first create a calculate column in the normal table to mark whether a product is in PRODUCTS table, then create a measure to get the first product value or "No product found".

Flag = IF('Table'[PRODUCT] IN VALUES(PRODUCTS[PRODUCTS]), 1, 0)

MeasureProduct =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[PRODUCT] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[NAME], 'Table'[DATE] ),
        'Table'[Flag] = 1
    )
VAR _firstProduct =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[PRODUCT], 'Table'[PRODUCT] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[NAME], 'Table'[DATE] ),
        'Table'[Flag] = 1
    )
RETURN
    IF ( ISBLANK ( _count ), "No product found", _firstProduct )

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous , Try a measure like

if(isblank(countx(filter(product, product[products]= max(Table[products])),Table[products]),"No product found",max(product[products]))

[Anonymous]

Hey @amitchandak, thanks for the reply.

With "product" you mean the table in the first image right? So "Table" should be the second image.

When I put it in as a measure I get the following error: 

---

A single value for column 'products' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.