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 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.
Solved! Go to 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.
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.
@Anonymous , Try a measure like
if(isblank(countx(filter(product, product[products]= max(Table[products])),Table[products]),"No product found",max(product[products]))
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.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |