Need help with Direct query tables and local tables when in limited relationship.

[Anonymous]

Hi All

There are 3 tables 'Task' and 'Localextension' connected in direct query and 'Merge1' is the local table. Relation ship is as below. I joined using Id column which is common in all 3 tables. 

I need the out put as below For each request(Name) and section respective email id. Email id is available in 'Task' table. Name is from 'Local extension' and Relevantsection from 'Merge1' table. Currently, used below formula in a calculated column to the get result. However there are multiple rows exit in task table for each request, i need to fetch all the rows available for that request id and section name.

NeeHelp_recp =

Var Result = CALCULATE(FIRSTNONBLANK(task[Need Help Recipients],4),

FILTER(task, task[Regarding] = Merge1[cmp_name.3] && task[Need Help Section] = Merge1[cmp_relevantsectionidname] ))

Please help me on this. I tried multiple way but due to direct query there of lot limitations.

[Anonymous]

It did not work for me, throwing error at Merge1[cmp_name.3] as column not found 

[Anonymous]

Hi, Thank you for the solution. It almost worked but i need all email id's as in a sperate row instead of concatenation. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try the steps below to get it:

1. Put the field [Need Help Recipients] of the table 'task' to replace the measure [NeeHelp_recp] on the visual

2. Create a measure as below to judge if the related mails fulfill the conditions

Flag =
VAR _tab =
    CALCULATETABLE (
        VALUES ( task[Need Help Recipients] ),
        FILTER (
            task,
            task[Regarding] = Merge1[cmp_name.3]
                && task[Need Help Section] = Merge1[cmp_relevantsectionidname]
        )
    )
RETURN
    IF ( SELECTEDVALUE ( task[Need Help Recipients] ), 1, 0 )

3. Apply a visual-level filter with the condition (Flag is 1) on the below visual

Best regards

[Anonymous]

Hi @Anonymous ,

Please update the formula of [NeeHelp_recp] as below and check if it can return your expected result...

NeeHelp_recp =
VAR Result =
    CONCATENATEX (
        FILTER (
            task,
            task[Regarding] = Merge1[cmp_name.3]
                && task[Need Help Section] = Merge1[cmp_relevantsectionidname]
        ),
        task[Need Help Recipients],
        "; "
    )
RETURN
    Result

If the above one can't help you get the desired result, please provide some sample data in your tables 'Task', 'Merge1' and 'Localextension'  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards