Forum Discussion

user01's avatar
user01
Icon for Resolver I rankResolver I
1 year ago
Solved

COUNTROWS with ALLSELECTED and additional conditions

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):

 

PRODUCTCOMPANYCOMPANY_ORDERYEARMONTHYRMOMONTH_TEXTSALES
PB220245202405MAY 
PA120245202405MAY18
PB220246202406JUN 
PA120246202406JUN16
PB220247202407JUL 
PA120247202407JUL12
PB220248202408AUG 
PA120248202408AUG16
DB220249202409SEP2
DA120249202409SEP14
EB220249202409SEP 
EA120249202409SEP2
PB220249202409SEP 
PA120249202409SEP12
DB2202410202410OCT4
DA1202410202410OCT16
EB2202410202410OCT 
EA1202410202410OCT8
PB2202410202410OCT 
PA1202410202410OCT16
DB2202411202411NOV 
DA1202411202411NOV2
EB2202411202411NOV 
EA1202411202411NOV8
PB2202411202411NOV 
PA1202411202411NOV14
DB2202412202412DEC4
DA1202412202412DEC6
EB2202412202412DEC 
EA1202412202412DEC6
PB2202412202412DEC 
PA1202412202412DEC6
DB220251202501JAN8
DA120251202501JAN6
EB220251202501JAN 
EA120251202501JAN 
PB220251202501JAN 
PA120251202501JAN12
DB220252202502FEB2
DA120252202502FEB6
EB220252202502FEB 
EA120252202502FEB 
PB220252202502FEB 
PA120252202502FEB24
DB220253202503MAR6
DA120253202503MAR18
EB220253202503MAR2
EA120253202503MAR4
PB220253202503MAR 
PA120253202503MAR6
DB220254202504APR4
DA120254202504APR18
EB220254202504APR 
EA120254202504APR4
PB220254202504APR 
PA120254202504APR12
DB220255202505MAY 
DA120255202505MAY6
EB220255202505MAY 
EA120255202505MAY2
PB220255202505MAY 
PA120255202505MAY4
Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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.

     

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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")