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

[Anonymous]

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

[Ashish_Mathur]

Hi,

This measure should work

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

Hope this helps.

[Anonymous]

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

[v-jiascu-msft]

Hi @Anonymous,

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

Best Regards,

[Anonymous]

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 · Accepted solution]

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,