Selecting a cell value in power bi table visual

[abhay03]

I have a table that has some errors which are logged in to another table with RowID and ColumnName.

Original Table:

Error Table:

In the error table I want to create a value field and show the corresponding value that caused the error. I have the RowID and ColumnName to fetch the value for each cell in error table. Is this possible with the Table visual?

[MFelix]

Hi @abhay03,

Add a measure to your error table with this sintax:

Find_field = 
SWITCH (
    TRUE (),
    CONTAINS ( 'Error', 'Error'[ColumnName], "Name" ), LOOKUPVALUE ( Original[Name], Original[RowID], MAX ( 'Error'[RowID] ) ),    
    CONTAINS ( 'Error', 'Error'[ColumnName], "Age" ), LOOKUPVALUE ( Original[Age], Original[RowID], MAX ( 'Error'[RowID] ) ),
    CONTAINS ( 'Error', 'Error'[ColumnName], "Gender" ), LOOKUPVALUE ( Original[Gender], Original[RowID], MAX ( 'Error'[RowID] ) )
    )

Result is below:

Regards,

MFelix

[abhay03]

Thanks @MFelix!

Your solution works well with fixed columns in table and lookup is from pre-determined table. In my case there are hundreds of columns and accordingly lookup is from different tables. I have expanded the scope of previous example:

Table1
RowID	Name	Age	Gender
1		30	Male
2	Eric	NA	M
3	Harper	45	Female

Table2
RowID	Address	Zipcode	State
1	New york	10001	Others
2	Indiana	60001/2	IL
3	Illinois	46001	IN

Table3
RowID	Item	ShipDate	Status
1	Pen	7/13/2016	Delivered
2	Notepad	7/14/2016	In transit
3		7/15/2016	Unknown

Error
RowID	Error	TableName	ColumnName	Value
1	Name is blank	Table1	Name
2	Gender is invalid	Table1	Gender
2	Age is invalid	Table1	Age
1	State is invalid	Table2	State
2	Zipcode invalid	Table2	Zipcode
3	Item is blank	Table3	Item
3	Status is invalid	Table3	Status

Here the need is to use the values dynamically in the DAX query, something like below:

CONTAINS ( 'Error', 'Error'[ColumnName], <Row 1 ColumnName value> ), LOOKUPVALUE ( <Row 1 TableName value>[<Row 1 ColumnName value> ], Original[RowID], MAX ( 'Error'[RowID] ) )

[v-caliao-msft · Accepted solution]

@abhay03,

CONTAINS ( 'Error', 'Error'[ColumnName], <Row 1 ColumnName value> ), LOOKUPVALUE ( <Row 1 TableName value>[<Row 1 ColumnName value>

We cannot use dynamic column name or table name in DAX currently. So for your scenario, I am afraid there is no a efficient DAX expression to achieve your requirement.

Thank you for your understanding.

Regards,

Charlie Liao