Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count and Filter in DirectQuery where Product Count greater than "n"

Hello All,

 

following situation:

 

We have a table like

ProductCategory | Product

A | A1

A | A2

A | A3

B | B1

B | B2

 

Now I want to show only ProductCategory where Count(Product) > 2, e.g. ProductCategory A

 

Is there any chance to do this in DirectQuery Mode (and not to use Power Query Editor)

 

I could think of possible solutions in Import Mode (e.g. New Column with Calculate etc) but in DirectQuery?

 

Thanks and Best Regards

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous ,

     

    There isn't any context in the Page or Report Level filters. So we can't use a measure there. There is a workaround. You can add a new table like below and use a measure instead. Please refer to the snapshot below.

    1. A new table.

    N

    1
    2
    3

    2. Create a measure.

     

    Measure =
VAR countProduct =
    COUNT ( directquery[Product] )
RETURN
    IF ( countProduct > MIN ( Table1[N] ), countProduct, BLANK () )

    3. Add the new column in a Page or Report filter.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    It seems the "n" is static. How about the filters? Please refer to the snapshot below.

    Count-and-Filter-in-Direct-Query-where-Product-Count-greater-than-n

     

     

    Best Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      thanks for your response.

      Tthis is possible in Visual Level Filter only.

      I am looking for a solution for Page or Report Level.

       

      Best regards

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous ,

         

        There isn't any context in the Page or Report Level filters. So we can't use a measure there. There is a workaround. You can add a new table like below and use a measure instead. Please refer to the snapshot below.

        1. A new table.

        N

        1
        2
        3

        2. Create a measure.

         

        Measure =
VAR countProduct =
    COUNT ( directquery[Product] )
RETURN
    IF ( countProduct > MIN ( Table1[N] ), countProduct, BLANK () )

        3. Add the new column in a Page or Report filter.

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    This measure should work

     

    Measure1 = if(COUNTROWS(Data)>2,COUNTROWS(Data),BLANK())

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI v-jiascu-msft ,

       

      very interesting solution. It works but produces a huge join (if used with a real table), which results in the 1Mio Row Problem (Thought this would only relate to the result set shown, but seems to make problems if the join result is > 1 Mio. even in the background)

       

      Ashish_MathurI Can not use this in a Report or Page Filter.

       

      Thanks for your help.

       

      Best Regards