Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Alternative High Performance Way to Filter Data?

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

 

Need Help
Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-kelly-msft
      Community Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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)