Alternative High Performance Way to Filter Data?

[Anonymous]

Hello everyone,

Weeks ago, I asked a question about "how to filter 1 domain only Search Term and Page with Sum of Impressions, Clicks".

The quetion is resolved in this post.

By creating new measure, the table can be well filtered.

Here is the sample PBI file.

Cout and filter, Query Must in Table = 
CALCULATE ( DISTINCTCOUNT ( Raw[Query] ), FILTER ( SUMMARIZE ( Raw, Raw[Query], "d0", COALESCE ( CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( Raw, Raw[Domian] = "domain0" ) ), 0 ), "d-total", VAR currentQuery = MAX ( Raw[Query] ) RETURN COALESCE ( CALCULATE ( DISTINCTCOUNT ( Raw[Domian] ), FILTER ( ALL ( Raw ), Raw[Query] = currentQuery ) ), 0 ) ), [d0] = 1 && [d-total] = 1 ) ) 

Issue:

Unfortunately, when I apply the DAX into real case, the performance issue comes.

There are over 20 million rows * 10 columns data in DB, after I applying the Measure into Table Visual Filter, I waited more than 0.5 hour without refreshed result.

So, I come again to ask, if there is an alternative high performance way to make it happen?

Expect Result:

There are 3-4 domains in the real data, what the expect result is:

to get all result in domain0 but not in domain1, or in domain1 but not in domain2 (compare 2 domains only).

It not necessary to compare all domains, which means filter out data in domain0 but not in domain1, neither in domain2, neither in domain3.

Thanks in advance for your ideas.

H

[Anonymous]

after hours running in real data, the visual finnally gives following error statement:

I am not sure because of the DAX performance issue or there is a way to filter top N items only. e.g. all Query with top 10 Page breakdown, or Top 100 Query with all Page breakdown, etc.

Thanks!

[v-kelly-msft]

Hi  @Anonymous ,

Try the measure below:

Measure _test = 
var d0=CALCULATE(DISTINCTCOUNT(Raw[Domian]),FILTER(ALL('Raw'[Query],'Raw'[Domian]),'Raw'[Query]=MAX('Raw'[Query])&&Raw[Domian] = "domain0"))+0
var d_total=CALCULATE(DISTINCTCOUNT(Raw[Domian]),FILTER(ALL(Raw[Query]),'Raw'[Query]=MAX('Raw'[Query])))+0
Return
CALCULATE(DISTINCTCOUNT ( Raw[Query] ),FILTER ( ALL (Raw[Query],'Raw'[Domian] ),d0 = 1 && d_total = 1)) 

Best Regards,
Kelly

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

[Anonymous]

Hi @v-kelly-msft ,

sorry I need to unmark your reply as solution at present, please let me clarify so that could have your further help.

The excpect result is to compare between 2 domains, but not "1 domain only", they are slightly different, as you can see, there are 3 domains, Domain0, Domain1, Domain2.

When use the solution you provided, if set "Domain0" in code, it is working perfect.

However, if set "Domain1" in code, there will be no data, as there is no search term is only in Domain1.  - But, if there is a place to set to compare between "Domain1" and "Domain2", from following screen capture you will see "Search Term 1" and "Search Term 12" meets the criteria.

The Expect Result is: I can set in the code to compare "Domain1" and "Domain2", so that to check which term is existing in Domain1 compare to Domain2, and return the sum of Impressions.

So, here is the expected reuslt:

Thanks in advance if there is further help you can provide to resolve the performance issue.

PS: (in this post you can see there is further code to compare 2 domains, but I will still meet following error when applying into the real data of over 20million records)

[v-kelly-msft]

Hi  @Anonymous ,

It's OK,sorry that I didnt help last time.

Could you pls provide some sample data in an editable format with expected output for test?

Best Regards,
Kelly

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

[Anonymous]

Hi @v-kelly-msft ,

Thanks for reply.

As in OP, please refer to below file as editable PBI file and sample data.

Download Sample File.

In this screen capture, right hand part is the Expect Result:

from the left Full List you can see, compare to domain1 and domain2, Search Term 1 shall be listed in Expect Result, because it only existing in domain1 but not in domain2 - although it's in domain0 as well, but we are comparing domain1 and domain2.

As in OP, you can see, someone helped me to get the code, but the problem is I meet following error, so that I am not sure if there is any other way can have a better performance DAX or etc.

Thanks again.

H