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.
Hope all are having a good day. My Search-FU is weak today and I need help.
I have a data model with two tables, a connection to a DB that is updated a few times per day. It also has a static Excel file for referencing customer names.
The Customer Name field from within the DB can have new entries added and is a simple text field. So, one entry might be ACME, the next ACME Inc. and the next Acme Co.
The spreadsheet aims to reduce the number of names displayed in the filter. In this case, we just want ACME to show and return all entries associated. The spreadsheet is manually updated periodically where column A is updated to match the Customer Name field from the DB and column B has the common name (ACME).
I am stuck on how to implement it. I am thinking that a new column in the DB model should be added with a measure that says If Customer Name is found in column A of the Excel table and there is data is column B, use B. Otherwise, use the original value from the DB. (In this case all of the varied Acme names have just Acme in column B.)
This should take care of the fact that new entries may not be found in the static file yet return the common name if it is.
I'm thinking it is a IF and Contains statement, I am just blanking on how to structure it.
Thanks for your time!
Trent
Solved! Go to Solution.
I would implement this as a custom column in Power Query.
if [Common] = "" then [DB] else [Common]
You could do the same thing as a calculated column in DAX
IF(ISBLANK([Common]), [DB], [Common])
I would implement this as a custom column in Power Query.
if [Common] = "" then [DB] else [Common]
You could do the same thing as a calculated column in DAX
IF(ISBLANK([Common]), [DB], [Common])
Greg,
I appreciate the quick response. While your answer was not the actual solution, you provided the trigger! Knocked me out of my singular thinking with your suggestions. Providing both of the implementation options was the extra bit.
It was actually a LOOKUP function that I used after some other column work.
Most appreciated and have a great day!
Awesome, I probably wasn't visualizing the problem properly in my head. Glad you were able to solve it though!
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 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |