Replace category with Category from another table

[Mburman-07]

Hi All,

I have two tables WIND_FAULT_CATEGORIES_GADS and WIND_FAULT_CATEGORIES_IEC. They both have a relationship to my events table using 1 to many. The WIND_FAULT_CATEGORIES_GADS joins to events on GADS Code and the IEC table joins on IEC code.

I'm trying to build a measure or column that will allow me to show the following:

If WIND_FAULT_CATEGORIES_GADS System = "Wind Turbine" then return the WIND_FAULT_CATEGORIES_IEC Category else give me the GADS System.

In SQL it would be a simple IF statement or a CASE statement but I can't figure out how to replicate this in PBI

Thanks in advance,

Mike

[Bibiano_Geraldo]

Hi @Mburman-07 ,

Option 1: Using a Calculated Column:

Result = 
IF (
    RELATED(WIND_FAULT_CATEGORIES_GADS[System]) = "Wind Turbine",
    RELATED(WIND_FAULT_CATEGORIES_IEC[Category]),
    RELATED(WIND_FAULT_CATEGORIES_GADS[System])
)

Option 2: Using a Measure:

Result Measure = 
IF (
    SELECTEDVALUE(WIND_FAULT_CATEGORIES_GADS[System]) = "Wind Turbine",
    SELECTEDVALUE(WIND_FAULT_CATEGORIES_IEC[Category]),
    SELECTEDVALUE(WIND_FAULT_CATEGORIES_GADS[System])
)

[Kedar_Pande]

@Mburman-07 

Calculated Column

FaultCategory = 
IF (
RELATED(WIND_FAULT_CATEGORIES_GADS[System]) = "Wind Turbine", 
RELATED(WIND_FAULT_CATEGORIES_IEC[Category]), 
RELATED(WIND_FAULT_CATEGORIES_GADS[System])
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[danextian]

Try

VAR _GADS =
    LOOKUPVALUE (
        WIND_FAULT_CATEGORIES_GADS[System],
        WIND_FAULT_CATEGORIES_GADS[event id], WIND_FAULT_CATEGORIES_IEC[event id]
    )
RETURN
    IF (
        _GADS = "Wind Turbine",
        WIND_FAULT_CATEGORIES_IEC[Category],
        WIND_FAULT_CATEGORIES_GADS[System]
    )

If this returns an error stating that a single value cannot be determined, it means that the result is more than one, an event id in WIND_FAULT_CATEGORIES_IEC returns more than one WIND_FAULT_CATEGORIES_GADS[System]. In this case, which among the values to pick?

[Mburman-07]

Hi @danextian 

Thank you for this, unfortunately this wouldn't work as each table has unique Event ID. In fact they don't share any common columns. The GADS table joins to events on Gads_Code = Gads_Event_Code and IEC joins on IEC_Code = IEC_Events_Code

[danextian]

Can you please a workable sample data (not an image), your expected result from that and the reasoning behind. We've been trying to hit at something without properly aiming at it.

[anmolmalviya05]

Hi @Mburman-07 , Please try to create below calculated column:

CombinedCategory =
IF(
RELATED(WIND_FAULT_CATEGORIES_GADS[System]) = "Wind Turbine",
RELATED(WIND_FAULT_CATEGORIES_IEC[Category]),
RELATED(WIND_FAULT_CATEGORIES_GADS[System])
)

[Mburman-07]

Hi @anmolmalviya05 

I tried this solution however the RELATED function expects just  a column name and this returns an error

"The column WIND_FAULTS_CATEGORIES_GADS[System] either doesn't exist or doesnt have a relationship to any table availablie in the current context.

[Uzi2019]

Hi @Mburman-07 

Create this Calculated Column:

Column = IF ( 'Table1'[System]= "Wind Turbine", Related('Table2'[Category]), 'Table1'[System])

I hope I answered your question!

[Mburman-07]

Hi @Uzi2019 

I have tried this solution and returned an error in the measure,

"A single value for column 'System' in thable R_WIND_FAULT_CATEGORIES_GADS cannot be determined.

[ryan_mayu]

you can try to use RELATED get the data from another table.

https://learn.microsoft.com/en-us/dax/related-function-dax?wt.mc_id=DP-MVP-5004616

if this does not work, pls provide some sample data and expected output