Calculated column or measure that returns values from Table A corresponding to certain values Tab B

[siamoaquile]

Hi,
i'd want a Calculated measure or column that returns values of column x of table A corresponding to certain values of another 'table B (for example). What dax should i write?

This is because I tried to write this function:

CampagnaZefir= IF(SELECTEDVALUE(TableB[CodZef]) IN {"2025","C25","F25","W25"},1,0)

and i put it as a filter

but the table shows filtered results only if I add the TableB[CodZef] column, otherwise the values of Table A do not appear. But I want the filtered values to tick without me putting the CodZef column. 

How can i do that? Thank you!!

[siamoaquile]

i tried related, relatedtable and lookup but if i put IN {"2025", "C25", "F25", "W25"}, there is an alert that says me that "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

[Anonymous]

Hi @siamoaquile 

Thank you very much bhanu_gautam for your prompt reply.

Allow me to add something:

Use the RELATED function to ensure there is a relationship between Table A and Table B. For example:

"TableA"

"TableB"

If you don't have a column in your data that would allow you to establish a relationship, you might consider creating a calculated column and using the LOOKUPVALUE function:

FilteredColumn = 
IF (
    LOOKUPVALUE(TableB[CodZef], TableB[ID], TableA[ID]) IN {"2025", "C25", "F25", "W25"},
    1,
    0
)

Regards,

Nono Chen

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

[bhanu_gautam]

@siamoaquile , You can try using a calculated column

CampagnaZefir =
IF (
RELATED(TableB[CodZef]) IN {"2025", "C25", "F25", "W25"},
1,
0
)

[siamoaquile]

i can't find the column in RELATED()

[AilleryO]

Hi,

Some liitle things to check :

1/ Do you have a relationship between the 2 tables in your model ?

2/ If no, you need to build the relationship in the model view before using related.
If yes, are you using the relationship from the table on the side 1 to the table on the side many, or from the table many to the table on the one side of relationship ?

RELATED can be used from the many (*) to one (1), and if you are on the table being on the 1 side and want to get info from the table on the many side, then you'll need RELATEDTABLE as your results will/might not be unique.

In case it might not be clear, herre is an image 😉

Hope it help