Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, Sorry for the simplicity of the question - I had a look through a few similar questions but couldn't find any solutions which helped me out.
I understand my tables are not of the easiest set up, but they need to stay in the current format to work with the rest of my report.
This is my simple example:
Put simply, I would like the name of the city , instead of the ID, when LevelOfDetail is Region.
I can't just link up the two tables and use region instead of attribute , as I would also like the attribute column to show for the other levelofdetail values. My two first approaches were to 1) - create a measure with an if function which used the reference to find the city when levelofdetail is city, and just used the existing attribute when not. 2) - write a left join between the two tables in power query ( these two tables will be import from sql) , and then sort the tables to the correct form using the toolbars in power query.
I couldn't get either to work, so if anyone has the solyution, that would be greatly appreciated.
Thanks a lot,
EHa
Solved! Go to Solution.
hi @EHa
for your case, I suggest you create a new attribute column in table2 by this logic:
New Attribute =
VAR _ID=IF('Table 2'[LevelofDetail]="Region",VALUE('Table 2'[Attribute])) RETURN
IF('Table 2'[LevelofDetail]="Region",LOOKUPVALUE('Table 1'[City],'Table 1'[ID],_ID),'Table 2'[Attribute])
Now when create a visual, use this new attribute.
and here is sample pbix file, please try it.
Regards,
Lin
hi @EHa
for your case, I suggest you create a new attribute column in table2 by this logic:
New Attribute =
VAR _ID=IF('Table 2'[LevelofDetail]="Region",VALUE('Table 2'[Attribute])) RETURN
IF('Table 2'[LevelofDetail]="Region",LOOKUPVALUE('Table 1'[City],'Table 1'[ID],_ID),'Table 2'[Attribute])
Now when create a visual, use this new attribute.
and here is sample pbix file, please try it.
Regards,
Lin
Hi Lin,
That's great, thanks a lot.
Appreciate the effort in remaking it too, very clear now.
Cheers,
EHa
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
144 | |
104 | |
103 | |
82 | |
70 |