Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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]) )
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],
","
)
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.
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 :
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |