COUNTROWS with ALLSELECTED and additional conditions

[user01]

I get some data via DirectQuery, but I have some dummy data below.

I want to count the number of rows for Product D and Product E returned by ignoring the matrix context and only applying the slicer filtering.

In 2024: when I deselect D and E, the value should be 0, but I am getting 16. If I deselect SEP through DEC instead, I get 0, which is expected and desired.

FYI, 16 is the total D+E rows in 2024. 16 is also the total P rows in 2024, which is confusing.

EDRows = CALCULATE(COUNTROWS('Table'), ALLSELECTED('Table'), 'Table'[PRODUCT] = "D" || 'Table'[PRODUCT] = "E")+0

Returned value desired is 0, but I get 16.

Returned value is 0, which is expected and desired.

Dummy data (YRMO was intended to be TEXT type):

PRODUCT	COMPANY	COMPANY_ORDER	YEAR	MONTH	YRMO	MONTH_TEXT	SALES
P	B	2	2024	5	202405	MAY
P	A	1	2024	5	202405	MAY	18
P	B	2	2024	6	202406	JUN
P	A	1	2024	6	202406	JUN	16
P	B	2	2024	7	202407	JUL
P	A	1	2024	7	202407	JUL	12
P	B	2	2024	8	202408	AUG
P	A	1	2024	8	202408	AUG	16
D	B	2	2024	9	202409	SEP	2
D	A	1	2024	9	202409	SEP	14
E	B	2	2024	9	202409	SEP
E	A	1	2024	9	202409	SEP	2
P	B	2	2024	9	202409	SEP
P	A	1	2024	9	202409	SEP	12
D	B	2	2024	10	202410	OCT	4
D	A	1	2024	10	202410	OCT	16
E	B	2	2024	10	202410	OCT
E	A	1	2024	10	202410	OCT	8
P	B	2	2024	10	202410	OCT
P	A	1	2024	10	202410	OCT	16
D	B	2	2024	11	202411	NOV
D	A	1	2024	11	202411	NOV	2
E	B	2	2024	11	202411	NOV
E	A	1	2024	11	202411	NOV	8
P	B	2	2024	11	202411	NOV
P	A	1	2024	11	202411	NOV	14
D	B	2	2024	12	202412	DEC	4
D	A	1	2024	12	202412	DEC	6
E	B	2	2024	12	202412	DEC
E	A	1	2024	12	202412	DEC	6
P	B	2	2024	12	202412	DEC
P	A	1	2024	12	202412	DEC	6
D	B	2	2025	1	202501	JAN	8
D	A	1	2025	1	202501	JAN	6
E	B	2	2025	1	202501	JAN
E	A	1	2025	1	202501	JAN
P	B	2	2025	1	202501	JAN
P	A	1	2025	1	202501	JAN	12
D	B	2	2025	2	202502	FEB	2
D	A	1	2025	2	202502	FEB	6
E	B	2	2025	2	202502	FEB
E	A	1	2025	2	202502	FEB
P	B	2	2025	2	202502	FEB
P	A	1	2025	2	202502	FEB	24
D	B	2	2025	3	202503	MAR	6
D	A	1	2025	3	202503	MAR	18
E	B	2	2025	3	202503	MAR	2
E	A	1	2025	3	202503	MAR	4
P	B	2	2025	3	202503	MAR
P	A	1	2025	3	202503	MAR	6
D	B	2	2025	4	202504	APR	4
D	A	1	2025	4	202504	APR	18
E	B	2	2025	4	202504	APR
E	A	1	2025	4	202504	APR	4
P	B	2	2025	4	202504	APR
P	A	1	2025	4	202504	APR	12
D	B	2	2025	5	202505	MAY
D	A	1	2025	5	202505	MAY	6
E	B	2	2025	5	202505	MAY
E	A	1	2025	5	202505	MAY	2
P	B	2	2025	5	202505	MAY
P	A	1	2025	5	202505	MAY	4

[v-hjannapu]

Hi @user01,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @OwenAugerfor your response regarding the issue.

You want to make sure that the visual context from ALLSELECTED remains active while also applying a specific filter for D and E products
You may try the following Measure :

EDRows NEST KEEPFILTERS = 
CALCULATE (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        KEEPFILTERS ( 'Table'[PRODUCT] IN { "D", "E" } )
    ),
    ALLSELECTED ( 'Table' )
) + 0

Please find the attached file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Harshitha
Community Support Team.

[Ashish_Excel]

Hi,

I do not understand.  In Dec 2024, the total of D and E is 16.  Why should 0 appear.  This is the measure i used

S = SUM(Data[SALES])

Measure = CALCULATE([S],Data[PRODUCT]="D"||Data[PRODUCT]="E")

[OwenAuger · Accepted solution]

Hi @user01 

If I've understood you correctly, you would like to restore the ALLSELECTED context of the visual, then intersect the PRODUCT filter { "D", "E" } with the existing filter.

I would suggest something like this with two nested CALCULATE calls:

EDRows NEST KEEPFILTERS =
CALCULATE (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        KEEPFILTERS ( 'Table'[PRODUCT] IN { "D", "E" } )
    ),
    ALLSELECTED ( 'Table' )
) + 0

Does this work as intended?

As a separate matter, you may also want to create appropriate dimension tables to avoid unwanted crossfiltering between dimension columns.