Forum Discussion
Measure Issue in visual table
Hi Saxon202202
Welcome to the Microsoft Fabric Forum,
Also thank you all for responding on this topic.
Regarding the measure to compare Stock and Qty values, the issue arises due to the many-to-one relationship between the Data and Report tables. When you pull non-aggregated fields (such as Qty) from the Data table, Power BI attempts to display all matching combinations based on the relationship.
In your case, the Item "123-01" in the Report table has two entries, while the Data table may contain multiple corresponding Qty values for the same item. As a result, Power BI cross-joins each matching Qty with the related Report rows, leading to duplicate lines in the visual.
In Power BI, row duplication occurs when non-aggregated columns are pulled from the "one" side of a many-to-one relationship, where the "one" side contains multiple matching rows.
To evaluate each record in the Report table individually without aggregation and to avoid duplicate lines, you should create a calculated column in the Report table.
Try using the LOOKUPVALUE function as follows:
Comparison = VAR DataQty = LOOKUPVALUE(Data[Qty], Data[Item], Report[Item]) RETURN IF(Report[Stock] >= DataQty, "Yes", "No")If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges.
Thank you for being part of the Microsoft Fabric Community.
Here it is.
Hi,
Thank you for your response. I would like to display the status for each individual line in the report table, so I want to avoid summing the Stock and Qty columns.
Is it possible to achieve the desired outcome without aggregating Stock and Qty for each line in the report table?
Could you also clarify how removing the aggregation (e.g., sum) for the Qty column might lead to the generation of multiple rows in the report table?
Desired Result:
You can choose not to summarize Data[Qty] and Report[Stock] columns but would it, in your case, make sense to compare the Qty of each item in Data against individual rows in Report?
Yes please. I like to know the status each individual rows.
Desired results :
Add the qty and stock columns but don't summarize them.
.