Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Convert SQL to Power BI

Hello,

 

I'm very new to Power BI, therefore please be indulgent with me 😄

Actually, I have 2 tables called Report and Mails. In the Report table I have a column UserId and in the Mails table I have a column called Body that might contain UserId.

I would like to convert this sql to Power BI

 

select Report.UserId, Mails.Body where Mails.Body LIKE '%' + Report.UserId + '%'; 

 

Is it possible?

Thx in advance.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Create a measure  like this and use with required columns

    calculate(count(Report[UserId]), filter(Mails, search(max(Report.UserId), Mails[Mails.Body],,0)>0), values(Report[UserId]) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak,

       

      First thank you for your prompt answer. I have tried like this

      Measure = calculate(count(Report[Racine]); filter(Mails; search(max(Report[Racine]); Mails[Body];,0)>0); values(Report[Racine]))

      Note that I have changed the colu,n name from UserID to Racine.

      But I got an error

      "The followingsystem error occurred:" and nothing else after the :

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak,

       

      By removing one extra "," I don't have the eror anymore.

      But actually, the measure gives me a number and I would like to have the actual text message in Mails[Body].

      And note that for 1 Report[Racine] it might have 0-n Mails[Body]

      Thx 

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        Hi Anonymous 

         

        You can add a column in the Mails table with the following DAX codes:

        UserId = DISTINCT ( FILTER ( Report, SEARCH ( Report[UserId], Mails[Body], 1, 0 ) > 0 ) )

        Or (if a body has multiple UserIds)

        UserIds = 
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Report[UserId] ),
        FILTER ( Report, SEARCH ( Report[UserId], Mails[Body], 1, 0 ) > 0 )
    ),
    [UserId],
    ","
)

         

        Then put UserId column and Body column into a table visual.

         

        You can also create a relationship between Report[UserId] and Mails[UserId], then you can put Report[UserId] in the table visual instead.

         

        Reference:

        SEARCH FUNCTION WITH A LIST VALUE - Microsoft Power BI Community

         

        Kindly let me know if this helps.

        Community Support Team _ Jing Zhang
        If this post helps, please consider Accept it as the solution to help other members find it.