Filter or Slicer: include data for text contained in specific value

[h_l]

Hello everyone,

There is a column called "Domains",

"Query" are those user searching terms, 

here is the sample file to download.

Is there a way to set slicer or filter to include data as following:

Create a visual table or matrix, if there is a record of the Query in domain1, include the query and display the result of the Query in 3 domains.

Data:

Expect Result:

As you can see, since highlighted terms are in domain1, then only display those terms and their result in 3 domains.

For the rest query, don't display and don't include them in any of calculation / counting such as Total.

Expected Result shall look like:

Thanks in advance for your inputs.

Have a good day.

H

[ryan_mayu]

@h_l 

you can try to create a measure and add that measure to filter.

Measure = if(ISBLANK(sumx(FILTER(Raw,Raw[Domian]="domain1"),Raw[Impressions])),"NO","Yes")

pls see the attachment below

[h_l]

Hi @ryan_mayu , there is an "issue".

It can only be used when "Query" field is in the table.

If I change the "Row" to other fields, the Measure will not work any more.

Is there a way to make it?

Thanks again.

H

[ryan_mayu]

@h_l 

i tried to change the query to page

the measure can also display the data that domain 1 is not blank.

[h_l]

Hi @ryan_mayu , you are right, but the if the purpose is to see the "by page" performance on All domains for those Query got impressions in domain1, that will be different.

That is saying, if change "Query" to "Page", while Measure is also applied, the number in domain0 and domain1, domain2 shall be same to "Query".

In another word, the following left-hand chart is applied Measure, which is correct at all.

But, when I replace the "Query" by "Day", as you can see on the right-hand table, it gives ALL result - the Measure is also applied in the right-hand table.

So, is there a way can make the Measure or any type of filter can be applyed?

Thank you.

[ryan_mayu]

@h_l 

you can try to create a column

scope = 
VAR _domain=maxx(FILTER(Raw,Raw[Query]=EARLIER(Raw[Query])&&Raw[Domian]="domain1"),Raw[Domian])
return if(Raw[Domian]="domain1","Y",if(_domain<>"","Y","N"))

and add that column to filter.

[h_l]

Hi @ryan_mayu , thanks again for your reply. Unfortunately, seems this is still different to expected result.

Afte apply the column, if there is no date selection nor not change the Row to Page, it's fine.

But let's say if I choose only 2 days in "Date", the result is like left-hand table.

As you can see, the rows and numbers marked with red "x" shall not be included as they get no data in domain1. - the right-hand table is the expect result.

Anyway, I appreciate your input, and if there is further ideas, please help to post.

Have a joyful day.

H

[ryan_mayu]

@h_l 

Let's make your request be clear.

1. you want to see all the queries that have domain 1 value.

2. you will change the query to other column and you want to see the total amount keeps the same. What's more you only want to see the rows which have domain 1 value?

[h_l]

Hi @ryan_mayu ,

Thanks for your reply again.

You are almost correct, the purpose is to have a slicer or a way to filter the stasted idea.

Probably I will change the "Row"' from "Query" to "Page", or "Date".

So in your 2nd screen cut in point 2, it will be:

It's because Query "a" has record in domain1, so what is "a" 's resuslt in other domain? 300 in domain 2, but when breakdown to "Page", it's in page d.

Same to Date: if adding Date info:

query	domain	page	value	date
a	0	c	100	1-Jan
a	1	c	100	1-Jan
a	2	d	100	1-Jan
b	0	d	300	1-Jan
b	2	d	500	1-Jan
a	1	c	100	2-Jan
a	2	d	100	2-Jan
a	2	c	100	3-Jan
b	0	c	100	3-Jan

The expect result is: (in 3-Jan, the "a" only get number in domain 2, but not in domain 1, as a result, the number shall not be included.

So, I thought a way to filter and slicer might be better since all the rest of data calculation will be impacted, no matter how to breakdown, or change the dimension field.

Appreciate your time and help.

H

[ryan_mayu]

@h_l 

I think the logics conflict.

for page, you want to display d which domain 1 is empty.

for date, you don't want to display 3-Jan which domain 1 is empty.

i think you need to align the logics.

Above is my own opinion. Since you have accepted the solution, I suggest you to open a new post and used the example you just proivded to see if any experts can provide you better solution. (It's because I think super users and community supporters seldom look the post which has a solution)

[h_l]

Hi @ryan_mayu , thanks for the input.

The logic is: if there is a query get number in domain 1, output it's result in other domains, no matter by which dimension.

So, if show these 3 tables, might help to understand:

Rawdata:

Output Results:

No breakdown:

By Page, breakdown by Query (to show why): because there is a query "a" which landing page is "d", and "a" meet the rule that it gets number in domain1.

Reverse Explain: so that can see why Page "d" shall be displayed. Same to above one, it is because there is a record where "a" get number in domain1 and it's landing page is "d".

Same to "By Date" in previous reply.

Anyway, I sincerely appreciate your time and advise.

Will open a new post.

Enjoy the rest of your day.

H

[ryan_mayu · Accepted solution]

@h_l 

I think the second solution meets your request. you can take a look. Just the layout is slightly different from your screenshot since we can't remove subtotal and only display total.

[h_l]

@ryan_mayu this perfectly output the expect results. Thank you so much for your help and time!👍

[ryan_mayu]

you are welcome

[h_l]

Just tried in the actual data, it does work well.

Thank you!

[ryan_mayu]

you are welcome