Forum Discussion
Comparing Values between tables
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.
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.
8 Replies
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.
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
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?
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
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.
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.
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.
-
