cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zarzuela
Regular Visitor

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.

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@Zarzuela , 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]) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 

Hi @Zarzuela 

 

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],
    ","
)

112703.jpg

 

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

112704.jpg

 

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.

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 :

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.