It seems like a simple caluclated column but ....

[johnmelbourne]

I have this table in a report. Both columns come from different tables but are related in the model, but not directly (that is, through another table).

I want to create a calculated column such that

IF DESCRIPTION = Low Density Area  AND Spectrum_location_CC = 14.5 to 31.3 GHz then return 100 OR

IF DESCRIPTION = Medium Density Area  AND Spectrum_location_CC = 14.5 to 31.3 GHz then return 200 OR

and so on.

Any suggestions how this might be achieved? As the columns are not from a directly related table, I am having issues, but I figure it should be possible as I can produce the data in a table, and should be able to work it out, but I can't at the moment.

Any assistance greatly appreciated.

Regards

John

[v-cherch-msft · Accepted solution]

Hi @johnmelbourne 

You may try to create a measure like below.Then drag it into the table visual.

Measure =
IF (
    MAX ( Table1[DESCRIPTION] ) = "Low Density Area"
        && MAX ( Table3[Spectrum_location_CC] ) = "14.5 to 31.3 GHz",
    "100 OR",
    IF (
        MAX ( Table1[DESCRIPTION] ) = "Medium Density Area"
            && MAX ( Table3[Spectrum_location_CC] ) = "14.5 to 31.3 GHz",
        "200 OR"
    )
)

Regards,

[Anonymous]

Hi

first of all RELATED works also across several tables, as long as there is a relationship with the correct arrows directions.
So if
A <- B <- C

you can write, in A table, REALTED(column in C)

Second, if you don't have a realtionship you can still use LOOKUPVALUE to find specific values anywhere in the model