Help with measure to create desired functionality

[adean137]

Hello,

I think my last post got lost in the Spanish Community, so posting this again:

I have three tables: Flag connected to Metrics with M:M on Keyword, Volumes connected to Metrics with M:M on Keyword. See tables below: 

Flag table:

Keyword	flag	retailer 1
google maps	Retailer	Google
amazon prime	Retailer	Amazon
Cheap ebay deals	Retailer	Ebay
buy clothes online	non-retail
where to buy online	non-retail
amazon vs google	Retailer	Amazon
amazon vs google	Retailer	Google

Metrics table:

Keyword	Domain	metric 1	metric 2	metric 3	retailer 2
google maps	Google com	10	40	70	Google
amazon prime	Google com	10	40	70	Google
Cheap ebay deals	Google com	10	40	70	Google
buy clothes online	Google com	10	40	70	Google
where to buy online	Google com	10	40	70	Google
amazon vs google	Google com	10	40	70	Google
amazon vs google	Google com	10	40	70	Google
google maps	amazon.com	30	60	90	Amazon
amazon prime	amazon.com	40	70	100	Amazon
Cheap ebay deals	amazon.com	50	80	120	Amazon
Cheap ebay deals	ebay.com	100	10	100	Ebay
buy clothes online	ebay.com	120	203	2030	Ebay
where to buy online	ebay.com	130	40	20	Ebay
amazon vs google	ebay.com	140	50	30	Ebay
amazon vs google	ebay.com	50	30	10	Ebay

Volumes table:

keyword	value
google maps	10
amazon prime	20
Cheap ebay deals	30
buy clothes online	40
where to buy online	20
amazon vs google	60

On my page, I have domain filter from the Metrics table and flag filter from Flag table. Domain can only be selected one at a time. For each domain, I would like to also filter this by retailer and non-retailer keywords. However, I only want the retailer flag to be applied for that given domain. For example, if google.com domain is selected and retailer flag is selected, I want to show keywords that have retailer flag AND Google as a retailer. Therefore although the keyword amazon prime has a retailer flag, it would not show up unless the amazon.com domain was selected. When non-retailer flag is selected, I would expect all non-retailer keywords matching the domain in Metrics table to show.

Desired output:

 

Any help would be much appreciated.

[v-lionel-msft]

Hi @adean137 ,

'"What about if I wanted to add a date column to the Volumes table, hence making it a M:M relationship. I tried this and it didn't work. "

Since your model does not contain date data, I did not consider the date when creating the meature. Please complete your model and your expected output.

Best regards,
Lionel Chen

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

[adean137]

Hi @v-lionel-msft ,

Thank you so much for your help with this! I can see that it works. 🙂

What about if I wanted to add a date column to the Volumes table, hence making it a M:M relationship. I tried this and it didn't work. 😞 

Thanks,

Alicia

[v-lionel-msft]

Hi @adean137 ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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