cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kane2k4 Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @kane2k4 ,

 

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.

 

 

Count and Filter in DirectQuery where Product Count greater than n2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

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

Hi @kane2k4,

 

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kane2k4 Frequent Visitor
Frequent Visitor

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

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

 

 

Community Support Team
Community Support Team

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

Hi @kane2k4 ,

 

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.

 

 

Count and Filter in DirectQuery where Product Count greater than n2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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

Hi,

 

This measure should work

 

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

 

Hope this helps.

kane2k4 Frequent Visitor
Frequent Visitor

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

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