Forum Discussion

sudhakar111's avatar
sudhakar111
Icon for Helper IV rankHelper IV
5 years ago
Solved

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 Produ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    sudhakar111
    Icon for Helper IV rankHelper IV
    5 years ago

    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's avatar
      v-alq-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        sudhakar111
        Icon for Helper IV rankHelper IV
        5 years ago

        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.