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

[v-stephen-msft · Accepted solution]

Hi @Mburman-07 ,

I treid to create some dummy data for reference.

The relationship:

WIND_FAULT_CATEGORIES_GADS:

WIND_FAULT_CATEGORIES_IEC:

The scenario on my side is that I want to check which shops my fruits or vegetables are sold in the GADS table.

If my sample data above is similar with yours, let's move on.

You can't use RELATED function in table GADS but you can use the RELATED in table IEC because of the relationship. In table GADS, a GADS code will have multiple IEC codes associated with it, which causes an error that if you use the RELATED function in table GADS , you cannot return a single value.

This is a reproduction of the issue.

To solve this, we can merge tables in Power Query.

Merge the Tables:

In Power BI Desktop, go to the Home tab and click on Merge Queries.
Select the Products table as the first table and the Categories table as the second table.
Merge them using the matching columns.

Expand the Merged Table:

After merging, you will see a new column with a table icon. Click on the expand icon (two arrows) next to this column.
Select Shops column and click OK.

At this time, you are very close to success, and you will get an error below when you close and apply, which is good to delete this relationship and it will be loaded successfully.

If you want DAX instead of the transformation in Power Query. You can create a calculated table with DAX to implement it.

Table = FILTER(CROSSJOIN('WIND_FAULT_CATEGORIES_GADS','WIND_FAULT_CATEGORIES_IEC'),[GADS Code]=[IEC code])

With such a calculation table, the result can be obtained.

The easiest way I think is just drag the fields from tables, and it will show the result because of your relationship connected.

Best Regards,

Stephen Tao

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

[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