Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

RELATEDTABLE in IF Statement

Hi,

 

Please will suggest how to use RELATEDTABLE function in Power BI IF Statment

 

Please note: In IF statement we have check other tables includes Dimension tables and Fact Tables

 

Herewith scenario:

 

PBI IF COLUMN=
if Committed (this column from Table 2)= Yes and Product (this column from Table 3)= 'Electronics' or 'Electricals'
    and ElecPrice (this column from Fact 1) >ElectricPric  (this column from Fact 2)  then  ElecPrce
   else ElectricPric 

Need Help

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous

        You can use RELATED Function instead of RELATEDTABLE function when add a column

    for example:

    I have four table as below

    and then create the relationship by ID as below

     

    then I add the column in table 2

    PBI IF COLUMN = IF (
    Table2[Committed ] = "Yes"
        && (
            RELATED ( Table3[Product ] ) = "Electronics"
                || RELATED ( Table3[Product ] ) = "Electricals"
        )
        && RELATED ( Fact1[ElecPrice ] ) > RELATED ( Fact2[ElectricPric  ] ),
    RELATED ( Fact1[ElecPrice ] ),
    RELATED ( Fact2[ElectricPric  ] )
)

    Result:

    here is pbix, please try it.

    https://www.dropbox.com/s/s7ukoarl7l8znon/RELATEDTABLE%20in%20IF%20Statement.pbix?dl=0

     

    If it is not your case, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much Lin.

       

      I can't use RELATED because in the data model it is Many to One relationship.

       

      Thank you

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous

             we need more detail steps for us. Could you please share your simple sample pbix file?

        You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

         

        Best Regards,

        Lin