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 mea...
Need Help
Show and Tell
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)

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        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!