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,
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 | |
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 |
Table 3 | |
retailer | domain |
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.
Solved! Go to 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.
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]))
Hello @v-janeyg-msft ,
Hope you are well.
I'm just wondering if you have any suggestions for achieving this functionality?
Thank you 🙂
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
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?
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.
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.
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.
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.
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
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...
@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:
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
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 | |
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 |
Metrics table:
keyword | domain | metric 1 | metric 2 | metric 3 | retailer 2 |
google maps | google.com | 10 | 40 | 70 | |
amazon prime | google.com | 10 | 40 | 70 | |
ebay cheap deals | google.com | 10 | 40 | 70 | |
buy clothes online | google.com | 10 | 40 | 70 | |
where to buy online | google.com | 10 | 40 | 70 | |
amazon vs google | google.com | 10 | 40 | 70 | |
amazon vs google | google.com | 10 | 40 | 70 | |
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! 🙂
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |