Please Help: create Measure to Sum if value contains in another column

[h_l]

Hi there,

in my other posts, i think the problem was described in unclear way, so I didn't really get the solution yet.

Here I try to clarify it in a easier way.

Here is the full sample data in PBI file with all columns.

In Table1, [ID] stores value, they are repeated some times, by [Week] or [Type]

the [ID] values contained in [Page] in Table2

Question:

Is there a way to create Measure, to Sum Impressions for the [ID] where under WK10 if they are contained in [Page]?

E.g. Sum Impression of [Page] contains [ID]=news012808 or news003863 as they are in WK10.

PS: here are the other posts

New Measure, search and return

Adding Trend Line， Track the Result by Group

Thanks for reading, appreciate in advance for the help and solution you may provide.

H

[Greg_Deckler]

@h_l I think this is a clearer explanation. If I understand this correctly, you would do something like:

Measure =
  VAR __ID = MAX('Table1'[ID])
  VAR __Table = FILTER('Table2',SEARCH(__ID,[Page],1,BLANK()) <> BLANK())
RETURN
  SUMX(__Table,[Impressions])

[h_l]

Hi @Greg_Deckler ,

Thanks for reply.

In the Measure, where to set limit the "WK10"?

In my OP was taking WK10 as example, but I need to create for the rest of weeks, e.g. WK12, WK14. And to add them into a trend line visual by date.

After applying your suggested Measure, they are not Sum in "WK" level.

It would be acceptable either create Measure for every WK one by one, or if there is a way can automatically Sum as "WK" level as well.

Thanks again!

H

[Greg_Deckler · Accepted solution]

@h_l I assumed that they would be part of the visual. I don't see any "week" information in your second table, does it exist? If so, you would do something like this:

Measure =
  VAR __ID = MAX('Table1'[ID])
  VAR __Week = MAX('Table1'[Week])
  VAR __Table = FILTER('Table2',SEARCH(__ID,[Page],1,BLANK()) <> BLANK() && 'Table2'[Week] = __Week)
RETURN
  SUMX(__Table,[Impressions])

It's the same exact pattern. You use the && for a logical AND in your filter.

[h_l]

Hi @Greg_Deckler , I hope you had a good weekend.

There is no "Week" info in Table2. But there is Date info. I didn't paste it in OP image.

On the other hand, the "Week" info shall not be used in Table2, it is used for decide "Which IDs in Table1 are need to be filtered and selected for Sum of lifetime Impression in Table2" (but not only Sum WK10 impression from Table2.

For example, if let's say create Meaure for"Wk12", then it is used for filter:

OK, choose the IDs: faq505081, news012808, deals0324 as they are existing in WK12 in Table1, then Sum their lifetime Impression from Table2.

Please let me know if there is a change needed in the DAX.

Thanks again for your help.

Have a good day.

[Greg_Deckler]

@h_l You would need to change the part of the FILTER week = week and perhaps use WEEKNUM. WEEKNUM function (DAX) - DAX | Microsoft Docs

Hard to really say. Data is in pictures and so hard to mock up. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[h_l]

@Greg_Deckler  Thanks for the kind remind! I've post another clearer one. (before posting another one, I'm not sure why I cannot reply here, always be warned flood post more than 1 time within 3600s)

[v-robertq-msft]

Hi, @h_l 

Glad to hear that you have posted another clearer case, would you like to mark Greg_Deckler’s reply as a solution to close this case if his reply has helped you in some way?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h_l]

Thanks for remind, although it's not the exact solution, but does help a lot!