Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
h_l
Post Patron
Post Patron

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

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:

h_l_0-1636411401099.png

 

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.

h_l_4-1636411928747.png

 

 

Expected Result shall look like:

h_l_3-1636411858074.png

 

 

Thanks in advance for your inputs.

Have a good day.

H

 

1 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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
ryan_mayu
Super User
Super User

@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")

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

h_l_0-1636426643715.png

 

Is there a way to make it?

Thanks again.

 

H

 

@h_l 

i tried to change the query to page

1.PNG

 

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

2.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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".

h_l_3-1636429042437.png

 

 

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.

h_l_0-1636428398078.png

 

h_l_2-1636428901668.png

 

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

Thank you.

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

h_l_1-1636467043818.png

 

 

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

Have a joyful day.

H

@h_l 

Let's make your request be clear.

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

1.PNG

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?

2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:

h_l_0-1636512283527.png

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:

querydomainpagevaluedate
a0c1001-Jan
a1c1001-Jan
a2d1001-Jan
b0d3001-Jan
b2d5001-Jan
a1c1002-Jan
a2d1002-Jan
a2c1003-Jan
b0c1003-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.

h_l_2-1636513055071.png

 

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

 

@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)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:

h_l_2-1636515345000.png

 

Output Results:

No breakdown:

h_l_0-1636515301062.png

 

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.

h_l_1-1636515331153.png

 

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".

h_l_3-1636515453486.png

 

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

 

@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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Thank you!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.