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.
I have a data table that has various info on it, all linked to location codes. I have a separate reference table that links those location codes to the common names of the locations. Sometimes there are instances where there is info on the data table that is linked to a code that is not truly a location, so not included on the reference table. What I would like to do is somehow include a label, i.e. 'misc', that is used for every instance when there is no location found, but I'm stuggling to do so. There isn't one or even several particular codes that I can put on the refernce table to address this as there are hundreds (maybe thousands) of potential codes that could show up on the data table.....
Any assistance is appreciated
Solved! Go to Solution.
Hi, You can use a calculated column like this
Location = IF ( RELATED ( Table2[Location] ) = BLANK (); "Misc"; RELATED ( Table2[Location] ) )
Regards
Victor
Lima - Peru
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Okay here's a sample of specific data to help with my issue. The first table is the location reference table. The second table shows the actual data in our system, the third column being what I'm trying to figure out. The relationship between the two tables is the Code. In this sample, the charge codes 'QM456' and 'CA456' do not have an associated location, so what I'd like to have shown (as I've manually entered in the second table to show what I'd like) is 'Misc' for the location. The issue is that QM456 and CA456 are just made up examples, I have no idea what the Codes will be that aren't associated with locations, but I do know that Codes will come throug that don't have a location, so I'm looking for a way to have any code that doesn't exist in the reference table default to a location of 'Misc'.
Code | Location |
TX123 | Austin, TX |
CO123 | Denver, CO |
CA123 | Sacramento, CA |
FL123 | Miami, FL |
Code | Amount | Location |
TX123 | $ 100.00 | Austin, TX |
QM456 | $ 100.00 | Misc |
CA123 | $ 100.00 | Sacramento, CA |
CA456 | $ 100.00 | Misc |
Hi,
Try this
=IF(ISBLANK(RELATED(Table1[Location])),BLANK(),RELATED(Table1[Location]))
Hope this helps.
Thank you both for the help, it worked
Hi, You can use a calculated column like this
Location = IF ( RELATED ( Table2[Location] ) = BLANK (); "Misc"; RELATED ( Table2[Location] ) )
Regards
Victor
Lima - Peru
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |