Forum Discussion
Searching across multiple rows to return one distinct value
Good afternoon,
In a last-ditch effort to get help with this, is there a way to have two tables, one that searches the "master table" to return a single value? To make it more challenging, it needs to search multiple columns, including one row value that displays numbers first, then the letter. I would like it to search for Cad Initial Cad Incident Type Description, Basic Incident Final CAD Dispatch Code, then District, but just the last letter in the district row matters. Ultimately returning a Logic Code number. Any advice would be great!! Thank you
Hi jscheid,
Thanks for your follow-up.
You can achieve the requirement of returning a distinct value from multiple rows using DAX. Here’s a recommended approach:
Option 1: Get the latest value per ID
Latest Value =
VAR MaxDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[ID]))
RETURN
CALCULATE(
FIRSTNONBLANK('Table'[Value], 1),
'Table'[Date] = MaxDate,
ALLEXCEPT('Table', 'Table'[ID])
)
Option 2: Get distinct values across multiple columnsDistinct Values =
DISTINCT(
UNION(
VALUES('Table'[Column1]),
VALUES('Table'[Column2]),
VALUES('Table'[Column3])
)
)These approaches should address the scenario you described. Please try them and let me know if you face any issues.
Thanks & Regards,
v-gandrathi
7 Replies
Hi jscheid
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi jscheid,
Thank you for your question.
The logic you mentioned sounds reasonable, but since it requires matching across several columns and extracting just the last letter of the district value, it would be helpful if you could provide a few lines of sample data from both tables. Please include about 5–6 rows each from your master and incident tables, showing columns such as Incident Type, Dispatch Code, District (or Zone), and Logic Code.
How to provide sample data in the Power BI Forum - Microsoft Fabric CommunityThank you.
Hi jscheid,
Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.
If you have any further questions, feel free to reach out anytime.
Thank you.
Here are both files if this helps, Thank you
Hi jscheid,
Thanks for your follow-up.
You can achieve the requirement of returning a distinct value from multiple rows using DAX. Here’s a recommended approach:
Option 1: Get the latest value per ID
Latest Value =
VAR MaxDate = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[ID]))
RETURN
CALCULATE(
FIRSTNONBLANK('Table'[Value], 1),
'Table'[Date] = MaxDate,
ALLEXCEPT('Table', 'Table'[ID])
)
Option 2: Get distinct values across multiple columnsDistinct Values =
DISTINCT(
UNION(
VALUES('Table'[Column1]),
VALUES('Table'[Column2]),
VALUES('Table'[Column3])
)
)These approaches should address the scenario you described. Please try them and let me know if you face any issues.
Thanks & Regards,
v-gandrathiHi jscheid,
Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.
Looking forward to your response.
Hi jscheid,
Just wanted to check regarding your question. We haven’t heard back and want to ensure you're not stuck. If you need anything else or have updates to share, we’re here to help!
Let us know if you need any additional support.
Thank you.
