Use the value of a column in table A in a calculated column in table B

[Anonymous]

Hi All,

I have 2 tables, A and B.

Each has a pass/fail column (call it column X).

I want to create a measure (or maybe a column) in my visualisation table that says IF X in A is pass OR X in B is pass, return pass.

Basically I want one measure to look at the value of 2 serparate columns in 2 separate tables and return pass if one of these columns has pass in it.

Overall Score = IF('Appended Pres'[MSCI] = "Pass" || 'Sheet0'[Sustainalytics]="Pass", "Pass", "Fail")

The above code does not work because it is a calculated column in the appended pres table but Sustainalytics is in a different Sheet.

Thank you in advance.

[Anonymous]

HI @Anonymous,

If you can please share some dummy data with same data structure to help us clarify your scenario and test on it.

How to Get Your Question Answered Quickly  

You can also try to use the following measure formula if it suitable for your requirement:

Measure =
VAR currA =
    SELECTEDVALUE ( TableA[Column] )
VAR currB =
    SELECTEDVALUE ( TableB[Column] )
VAR list =
    FILTER ( { currA, currB }, [Value] <> BLANK () )
RETURN
    IF (
        COUNTROWS (
            FILTER ( ALLSELECTED ( TableA ), [Column] IN list && [StatusA] = "Pass" )
        ) > 0
            || COUNTROWS (
                FILTER ( ALLSELECTED ( TableB ), [Column] IN list && [StatusB] = "Pass" )
            ) > 0,
        "Pass",
        "Fail"
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Thanks for your help. I tried that measure but recieved Fails for all entries.

What is the StatusA and StatusB in the code? is it the column that says pass fail? (in which case it is the same as currA currB)

Each ID has 3 ratings, MSCI and Sustainalytics are in one table (from one excel sheet), ISS is from another sheet. There is a relationship between the ID's in the table.

I want a new column in black that says pass if one of the ratings for a given ID is pass.

Thank you

[Anonymous]

Hi @Anonymous,

>>What is the StatusA and StatusB in the code? 

Status means the column that your table stores the status. 

According to your snapshot, it seems like two status fields stored in table A, one status fields stored in table B, right? (TableA: ID, MSCI, Sustainalytics; TableB: ID, ISS)

If this is a case, I'd like to suggest you try to use below calculated column formula to 'table A' if it suitable for your requirement:

Overall =
VAR temp =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( TableA, [ID] = EARLIER ( TableA[ID] ) ),
            "ISS", LOOKUPVALUE ( TableB[ISS], TableB[ID], TableA[ID] )
        ),
        "Status", [MSCI] & "," & [Sustainalytics] & "," & [ISS]
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( temp, SEARCH ( "Pass", [Status], 1, -1 ) > 0 ) ) > 0,
        "Pass",
        "Fail"
    )

Regards,

Xiaoxin Sheng

[amitchandak]

Two get the result from two tables, either you have to move one measure to another table or you have to group them together using values and summarize to do the action.

In this file refer 4 ways of datediff.  that will give a good idea of how to do it. If not share table structure

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Hi Amit, unfortunately I cant open this on my desktop. Can you share pictures? I have provided more information in my reply to a previous post.