Unable to Filter Visual using Indirect RelationShip

[SwatKat]

Hi,

Hoping to get some help from this wonderful community. Below is the snapshot of my data model

Successorders => cases ( Many to 1 Single filter Direction)

FailureOrders => cases ( Many to 1 Single filter Direction)

Cases => account( Many to 1 Single filter Direction)

Customer=> Account ( Many to 1 Single filter Direction)

I have created this calculated Column in Cases table to look up in failure/success in those tables and update value

CaseFailureSuccess = IF(LOOKUPVALUE('FailureOrders'[Reason],FailureOrders[SalesForceCaseNumber],'Cases'[CaseNumber])=BLANK(),"Success",LOOKUPVALUE('FailureOrders'[Reason],FailureOrders[SalesForceCaseNumber],'Cases'[CaseNumber]))

This is how the Column is used 

Issue is = > I am unable to filter this visual using Columns from Customer Table. What am i missing here?

Also I am unable to add these columns together in a table visual 

Please help

[Anonymous]

Hi @SwatKat 

1.I have the following question:

The lookup() function can only return single value, the relationship between the FailureOrders and  cases is many-to-one, so when you use the funciton in case table, it cannot return the value, because there are multi related rows in FailureOrders table. How the column work, can you provide some sample output of the calculated column?

2.If you want to filter the column, try to change the filter direction to both between Customer and Acount table.

Best Regards!

Yolo Zhu

[vaibhavkale570]

Hi,

Based on your data model and the calculated column you've created, it seems like you want to filter the visual using columns from the "Customer" table. However, the calculated column "CaseFailureSuccess" is defined in the "Cases" table, and direct filtering from the "Customer" table might not work in this case.

To be able to filter the visual using columns from the "Customer" table, you can follow these steps:

1. Create a relationship between the "Cases" table and the "Customer" table: It looks like the relationship already exists, based on the "Many to 1 Single filter Direction" description in your data model.

2. Create a new calculated column in the "Customer" table: Open the "Customer" table in Power BI and create a new calculated column, which references the "CaseFailureSuccess" column from the "Cases" table using the RELATED() function. The RELATED() function will follow the relationship between the two tables and fetch the corresponding value.

CustomerCaseFailureSuccess = RELATED('Cases'[CaseFailureSuccess])

3.Now, you can use the "CustomerCaseFailureSuccess" column from the "Customer" table to filter the visual.

By creating the calculated column in the "Customer" table, you can use it as a filter in your visual, and it will correctly follow the relationships established in your data model.

[SwatKat]

Hi Vaibhav,

Thanks for your response. This is the error that i get when i try to create calculated column in customer table

Also If i want to show Customer Name , Account Name and Case Number it looks like impossible. It fails with Error RelationShip missing