Forum Discussion
Measure Issue in visual table
Hi,
I have a two tables; Data and Report. The data table contains unique value, while the report table has duplicate entry, Both tables have a many-to-one releationship. Because of this relationship, I can drag the necessary columns (State, Id, Qty and Type) from the data table into the report table in the visual table.
I want to create a measure for a visual table that evaluates the Stock and Qty columns from the Data and Report tables based on the Item column. The measure should display 'Yes' if the Stock value is greater than or equal to the Qty value; otherwise, it should display 'No'.
I tried using the following measure to achieve this, but I encountered an error (please refer to the attached snapshot)
Desired Result: (I prepare manually)
| Record Number | Item | Name | State | Bin | Stock | Status |
| 72170 | 123-01 | Tyre-01 | CHE | GEN01 | 1000 | Not Okay |
| 78521 | 123-01 | Tyre-01 | RCB | GEN02 | 500 | Ok |
| 63476 | 123-02 | Tyre-02 | CHE | GEN03 | 1000 | Ok |
| 80012 | 123-02 | Tyre-02 | CHE | GEN04 | 5000 | Not Okay |
| 78123 | 123-03 | Tyre-03 | CHE | GEN05 | 50 | Ok |
| 45211 | 123-05 | Tyre-05 | RCB | GEN06 | 50 | Not Okay |
| 70234 | 123-05 | Tyre-05 | RCB | GEN07 | 80 | Not Okay |
| 74897 | 123-10 | Tyre-10 | RCB | GEN08 | 150 | Ok |
Attached the Power BI file for your reference.
https://www.dropbox.com/scl/fi/emaip6z0cyyps5jyg9m2t/Table_Visual1.pbix?rlkey=eu1ts7b40k5356t07xdl9t694&st=5i2f4glu&dl=0
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.
18 Replies
If there is an existing relationship between the two tables, you can just compare the aggregates.
Comparison = VAR _ReportStock = SUM ( Report[Stock] ) VAR _DataQty = SUM ( Data[Qty] ) RETURN IF ( _ReportStock > _DataQty, "Yes", "No" )Thanks for your reply.
Could you please share the PBI file, some reason still I am getting lot of duplicate lines.Here it is.
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.
Hi Saxon202202
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.v-karpurapud, I am really sorry for the late reply. Usually I received email notification but some reason I didn't received it. Thanks for your reply and support.
Var stock = sum( report[stock] )
Var qty = sum( data[qty] )
Return
If( qty <= stock, "yes", "no")
Hi, Thanks for your reply. I would like included item as well in the measure. Also, I would like to get the status each lines rather than sum of the qty.
When included in a table with the item field present the sums will be per item as all the field for that row form a filter context for the measure
Hi,
In that PowerBI file, show the expected result very clearly.
Thanks for your reply.
Column name "Status" is my desired result (Please refer the snapshot)
Hi Saxon202202
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
