Data Mapping based on other table's field name

[CoTheiss]

Hi Community ! 

As my BI-experiences are more with other tools than PowerBI, I am struggling on this one as I think it should work somehow:

I do have two tables:

Data

DataID	Column2	Column3	Column4
1	Text123	<null>	Text789
2	<null>	Text456	Text789

Scoring

ScoringID	DataColumn	Score
1	Column2	10
2	Column3	5
3	Column4	20

My issue:

I want to apply a scoring for DataQuality for one of my tables.

Meaning: I have a defined Scoring table applying a score per specific column that should be applied if the datafield is not null.

Based on the example posted, DataID 1 will have a score of 30, while DataID 2 should have a scoring of 25.

So I at least think, that I need something like

if(Data.ColumnName() = [Scoring].[DataColumn], if(isnull([Data].[Column2]),0,1)*[Scoring].[DataColumn])  

However, I am stuck due to the lack of PowerBI knowledge as I do not know where exactly to apply and as well how to get this comparision with the field name.

Hope someone can help me out here 🙂

Also, if there are any other or built-in options to do this - very appreciated!

[PawarNovil · Accepted solution]

Hello @CoTheiss 

You can create a calculated column  in your Data Table as

Score =

IF('DataID'[Column1]=BLANK(),0,CALCULATE(sum(Score[Score]),FILTER(Score,Score[Datacolumn]="column1")))+         IF('DataID'[Column2]=BLANK(),0,CALCULATE(sum(Score[Score]),FILTER(Score,Score[Datacolumn]="column2")))+

      IF('DataID'[Column3]=BLANK(),0,CALCULATE(sum(Score[Score]),FILTER(Score,Score[Datacolumn]="column3")))

--------------------------------------------------------------------------------------------------

Also you can achive this by unpivoting Data table col1, col2, col3 in advance editor.

After that you need to merge data table and score table on column "Datacolumn". 

After merging your Data table look like this, then you can aggregate the data or create measure to get your desired result.

Regards,

Novil

If I answer your question, please mark my post as a solution.

[CoTheiss]

Hi @PawarNovil 

I just tried the first approach with the calculated column and it works! 

I also learned, that having a datafield with len(Field)=0 does not neccessarily equals blank() - as I needed to adapt the formula to if('Data'[Column2]="",...) 

I did not try the second approach (yet) as the first one was sufficient already (and my dataset is quite wide and long) 

[BeaBF]

@CoTheiss Looks to me like this is only possible when you transpose Table Data:

using DataColumn as key between Data Table and Scoring Table.

BBF