Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    Anonymous
    Not applicable
    3 years ago

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