Comparing Values between tables

[sudhakar111]

Hello All, I need some help with a requirement. I have 2 tables one is fact table and other is a reference table. The fact table is like the below table.

Rep Name	Place	State	City	Date	Product	Value
Dave	Market	New York	New York	3/1/2021	Congelli F	0.5
Steve	Center	Georgia	Atlanta	3/1/2021	Congelli G	1.5
Bill	Down	Texas	Austin	3/2/2021	Flan	2.5
Alex	Town	Washington	Seattle	3/3/2021	Gelatin	2.0

The data needs to be displayed as matrix format. On the matrix, product column values are shown as separate columns, like the attached image.Matrix

The Product values have to be comapred with a refrence table,which has product and value. If the reference table value is less for a product the value has to be shown in red. The reference table just have product and value columns.Linking both the tables based on product. I tried the related function to compare the values but it doesnt seem to be working.

[v-alq-msft · Accepted solution]

Hi, @sudhakar111 

You may create a measure as below. The pbix file is attached in the end.

Visual Control = 
IF(
    SUM('Fact'[Related])<SUM('Fact'[Value]),
    "red"
)

Then you can set conditional format as below.

Result:

For further information, please refer to the following document.

Use conditional formatting in tables 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sudhakar111]

Thanks a lot @v-alq-msft, The solution works perfectly on the same data. Tried to implement it on the original dataset. The related function does not seem to be working. Please find the screenshot attached.

[v-alq-msft]

Hi, @sudhakar111 

Please check the following considerations about Related function:

• The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship.

• When the RELATED function performs a lookup, it examines all values in the specified table regardless of any filters that may have been applied.

• The RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function. A table scanning function, such as SUMX, gets the value of the current row value and then scans another table for instances of that value.

• The RELATED function cannot be used to fetch a column across a limited relationship.

If it is a measure, the row context doesn't exist. So the error occurs.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sudhakar111]

Thanks a lot. The mistake i did was creating the related function on the reference table. When is created the related function on fact table it worked.

[sudhakar111]

Thank you both, Created a sample file. Here the values on the matrix have to be compared with the reference table values.If the values are greater than the reference table, those values have to be highlighted in red.

https://drive.google.com/file/d/1vlo4r-73T7TcohsehGp8jctn9S60bNKS/view?usp=sharing

[vanessafvg]

how are you trying to compare it can you show what you have done and demonstrate what are trying to do, what is yoru expected result?

[amitchandak]

@sudhakar111 , related or related table can work depending on join type 1-M or M to1

refer if this video can helphttps://www.youtube.com/watch?v=Wu1mWxR23jU

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[sudhakar111]

Created a sample file. Here the values on the matrix have to be compared with the reference table values.If the values are greater than the reference table, those values have to be highlighted in red.

https://drive.google.com/file/d/1vlo4r-73T7TcohsehGp8jctn9S60bNKS/view?usp=sharing