Searching across multiple rows to return one distinct value

[jscheid]

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 

[v-sgandrathi · Accepted solution]

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 columns

Distinct 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

[v-sgandrathi]

Hi @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.

[v-sgandrathi]

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.

[v-sgandrathi]

Hi @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 Community

Thank you.

[jscheid]

Here are both files if this helps, Thank you 

90%-times-by-calls_2025-08-12_180205.xlsx

Logic Code.xlsx

[v-sgandrathi]

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.

[Ritaf1983]

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/1447523