Show values if certain conditions are met

[adean137]

Hello,

Hoping someone can help me figure this out. I've tried a few options but none of them are efficient and using too much memory. For simplicity, I've outlined some sample tables and data below:

Table 1 and Table 2 are linked by keyword. Table 2 and Table 3 are connected by retailer.

Table 1
keyword	value
google maps	10
amazon prime	20
ebay cheap deals	30
buy clothes online	40
where to buy online	50
amazon vs google	60

Table 2
keyword	flag	retailer
google maps	retailer	Google
amazon prime	retailer	Amazon
ebay cheap deals	retailer	eBay
buy clothes online	non-retailer
where to buy online	non-retailer
amazon vs google	retailer	Amazon
amazon vs google	retailer	Google

Table 3
retailer	domain
Google	google.com
Amazon	amazon.com
eBay	ebay.com

I have a table visual with keyword and value as columns. I also have domain and flag filters on the page. When domain google.com is selected and flag retailer is selected, this is the desired outcome:

keyword	value
google maps	10
amazon vs google	60

When non-retailer flag is selected, I would expect the non-retailer keywords to show. 

Therefore even though other keywords have the retailer flag, I only want the retailer flag to be applicable to the selected domain, in this case I have selected google.com domain on the page, which is linked to the Google retailer. If I were to select ebay.com domain and retailer flag, I would expect only the keyword ebay cheap deals to show up.

Please let me know if you have any ideas!

Thanks in advance.

[adean137]

Hi @v-janeyg-msft

Thanks for your help with that. It worked very well. 🙂

If I wanted to show the metrics (in the Metrics table) for only branded keywords (where Measure = 1), how can I achieve this at a domain level? For example, for the domain google.com I want to see the sum of metric 1 for branded keywords (where Measure = 1).

I have tried the measure below but it didn't work.

was __table =

ADDCOLUMNS (SUMMARIZE (

'Metrics',

'Metrics' [keyword]),

"desired_metric", SUM ('Metrics' [metric 1]),

"check", [Measure])

RETURN

IF (HASONEVALUE ('Metrics' [keyword]), [Measure], SUMX (__ table, [metric 1]))

[adean137]

Hello @v-janeyg-msft ,

Hope you are well.

I'm just wondering if you have any suggestions for achieving this functionality?

Thank you 🙂

[v-janeyg-msft]

Hi, @adean137 

Try like this in card:

Measure =
VAR tab =
    FILTER (
        SUMMARIZECOLUMNS (
            Metrics[keyword],
            Metrics[retailer 2],
            Metrics[metric 1],
            Flag[retailer 1],
            "sum", SUM ( Volumes[value] )
        ),
        [retailer 1] = [retailer 2]
    )
RETURN
    IF (
        SUMX ( tab, [metric 1] ) = BLANK (),
        SUM ( Metrics[metric 1] ),
        SUMX ( tab, [metric 1] )
    )

By the way,There are two sets of data in the table that are exactly the same, which may affect the results.

Best Regards

Janey Guo

[adean137]

Hi @v-janeyg-msft ,

Many thanks again for the response. 

I have tried to create this measure and add it to my table visual but I'm getting an error (see screenshot). In the table visual I also want to have domain as a field. Should this work with the measure you suggested?

Thanks for pointing out the duplicate data - this has been fixed so this isn't causing the issue.

Do you have any ideas?

[v-janeyg-msft]

Hi, @adean137 

In the previous reply, you mentioned that you only want to display the value in the card, which I did.The meaure is for card.

If you want to show field in table ,you only need to drag field to table.

Like this:

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[v-janeyg-msft · Accepted solution]

Hey, @adean137

It's a pleasure to answer for you.

According to your description, I think you can create a measure, then use it in the filter pane.

Like this:

Measure =
VAR a =
    MAX ( Metrics[retailer 2] )
VAR b =
    MAX ( Flag[retailer 1] )
RETURN
    IF ( a = b || b = BLANK (), 1, 0 )

Here is my sample .pbix file. I hope it helps.

If you do not solve your problem, please feel free to ask me.

Best regards

Janey Guo

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[adean137]

Hi @v-janeyg-msft

Thanks for your help with that. It worked very well. 🙂

If I wanted to include this in a measure for a metric card to filter the keywords to only sum metric 1 (in the Metric table) for those keywords where Measure = 1, how can I achieve this?

I have tried the measure below but it didn't work.

was __table =

ADDCOLUMNS(SUMMARIZE(

'Metrics',

'Metrics'[keyword]),

"desired_metric", SUM('Metrics'[metric 1]),

"check", [Measure])

RETURN

IF(HASONEVALUE('Metrics'[keyword]), [Measure], SUMX(__table, [metric 1]))

[Ashish_Mathur]

Hi,

Brind over domain from Table3 to Table2 using RELATED() or LOOKUPVALUE() function.  Bring over domain and retailer from Table2 to Table1 using RELATED() or LOOKUPVALUE() function.  Now that all you want is from Table1, build your visual from Table1.

[NamishB]

Hi @adean137 - If I understood this correctly, you are referring to Edit interactions

Based on selection of one slicer you want to stop filtering other slicer values?

For Eg:

Ebay with Retailer shows up ebay Cheap deals only as below:

Google with Retail option selected (As in your example)

If this is the desired output, You can click on domain Slicer, Top banner Format button >Edit interaction and click Stop filter from Flag Slicer. You can do same way from flag slicer to Domain slicer as well.

Hope this helps, if not please elaborate your requirements.

Cheers,

-Namish B

[adean137]

Hi @NamishB ...

Not sure if you got my post... but just in case https://community.powerbi.com/t5/Translated-Spanish-Desktop/Mostrar-valores-si-se-cumplen-ciertas-co... 

[adean137]

@NamishB  please let me know if you got my last response. 🙂 

Here is my file https://we.tl/t-Ly9qK1wi4k 

And here is what I'm trying to achieve:

[NamishB]

Hi @adean137 - Can you share what relationships you using between Metrics and Flag tables?

I can see M: M but is this on Keywork Or Retailer2/Retailer1?

I think the relationship is wrong and this is causing confusion.

Cheers,

-Namish B

[adean137]

Hi @NamishB 

Thanks so much for your response. I'm so sorry, but I gave incorrect information in my first note. Please see below updated requirements:

Flag table:

keyword	flag	retailer 1
google maps	retailer	Google
amazon prime	retailer	Amazon
ebay cheap deals	retailer	eBay
buy clothes online	non-retailer
where to buy online	non-retailer
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
ebay cheap 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
ebay cheap deals	amazon.com	50	80	120	Amazon
ebay cheap 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:

keywordvalue

google maps	10
amazon prime	20
ebay cheap deals	30
buy clothes online	40
where to buy online	50
amazon vs google	60

Data model:

Current output:

Desired output:

Please let me know if this makes sense? 

Thanks agian! 🙂