Measure Issue in visual table

[Saxon202202]

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=eu1ts7b40k5356t07xdl9t...

[v-karpurapud]

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.

[Saxon202202]

@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. 

[v-karpurapud]

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.

[v-karpurapud · Accepted solution]

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.

[danextian]

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" )

[Saxon202202]

Thanks for your reply.
Could you please share the PBI file, some reason still I am getting lot of duplicate lines.

[danextian]

Here it is.

[Saxon202202]

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:

[danextian]

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?

[Saxon202202]

Yes please. I like to know the status each individual rows. 

Desired results :

[danextian]

Add the qty and stock columns but don't summarize them.

.

[Saxon202202]

Yes, I did. I apply do not summary of qty and stock columns in visual table but it's showing lot duplicate columns for the same line. Could you please explain why it's returned so many duplicates lines.

In report table 123-01 has two lines only. 

[Ashish_Mathur]

Hi,

In that PowerBI file, show the expected result very clearly.

[Saxon202202]

Thanks for your reply. 

Column name "Status" is my desired result (Please refer the snapshot)

[Deku]

Var stock = sum( report[stock] )

Var qty = sum( data[qty] )

Return

If( qty <= stock, "yes", "no")

[Saxon202202]

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. 

[Deku]

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

[Saxon202202]

I try to apply your measure and it's generate lot of duplicated lines. (Refer the snapshot)

Any idea why it's generting ultiple rows? Please