Forum Discussion
Data Mapping based on other table's field name
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!
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.
3 Replies
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.
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)
