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.
hey,
I have to tables from two different sources and one column contains a text which contains an ID that is defined in another table.
(I cannot publish a Message with tables - i am getting errors)
Source 1:
Company Name Sponsor (SP-ID) NL-ID
kuwerhgidufg SP0001 NL-0001
keurghk SP0002 NL-0008
... .... .....
Source 2 (Newsletter):
Title Openers URL
ifuwarhefkl 267 htt...text...NL-0001... .
luadrhoi 23829 htt.....NL-0008...
iufhsdfkön 12882 htt... NL-0001
... ... ...
-> I would like to have a third table or measurement that results in:
Sponsors Total Openers
SP0001 13.149
SP0002 .....
The problem is that these to tables so far cannot be related in any way.
In excel I would build something like "if Column [URL] contains [NL-ID] then sum up column [Openers]"
I am looking forward to solutions!
Solved! Go to Solution.
@SarahAlsterspre , You can have column like this in a table 1
sumx(filter(Newsletter, search( Source1[NL-ID], Newsletter[URL],,0)>0),Source1[Openers])
refer: https://www.youtube.com/watch?v=czNHt7UXIe8
@SarahAlsterspre , You can have column like this in a table 1
sumx(filter(Newsletter, search( Source1[NL-ID], Newsletter[URL],,0)>0),Source1[Openers])
refer: https://www.youtube.com/watch?v=czNHt7UXIe8
Hey @SarahAlsterspre ,
Okay, so I think I see what you are going for here and there are a few different ways you can go about it. The solution I've provided is not the most eqloquent or complex but should meet the outcome you listed.
I've used the following sample data for this solution
So we can actually make a measure like the following to get the outcome table you were looking for without connecting anything. In this case this measure would only work properly in a table format and in this example is calculating the average opener number
TheMeasurement =
var TheNL_ID = MAX('Source 1'[NL-ID])
return
CALCULATE(AVERAGE('Source 2'[Openers]), CONTAINSSTRING('Source 2'[URL], TheNL_ID))
Let me know if that solution works or if we want to go back and tinker with it some more
You can use this measure:
_Openers =
VAR _ndID = SELECTEDVALUE('Table'[NL-ID])
RETURN CALCULATE(SUM('Table (2)'[Openers]), CONTAINSSTRING('Table (2)'[URL], _ndID))
If you have a patterns, you can split the url column and related both tables, avoid any problem.
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 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |