Dynamic Count of Rows

[Anonymous]

Hi PBI Community,

I'm wondering whether you could help me with a problem I came across recently. I do have a lengthy DAX measure to correctly calculate a metric at different hierarchy levels and in a different filter context, however it's not always working right. Having investigated the issue I discovered the problem is with a small inner DAX measure that is supposed to calculate count of rows. The scenario is similar to the below:

DAX behind CountRows is:

CountRows = CALCULATE(COUNTROWS(Table1),
ALLEXCEPT(Table1, Table1[Product]))

This gives count per product which is great, however I would like this count to adjust based on filters I'm applying on Table1 i.e.  If I choose Type='Beginner' I would like to see the below output:
 

Product                Category               Type                      Price                 CountRows
Snow Board         On Piste                Beginner               100                    2
Snow Board         On Piste                Beginner               120                    2
Wake Board         Smooth                 Beginner                150                   1

Current DAX measure will always show 3 rows for Wakeboard and 4 rows for SnowBoard like this:
 

I would really appreciate your help on this.

Many Thanks,

[parry2k · Accepted solution]

@Anonymous try this measure, change table and column name as per your data model.

Count of Products = 
VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) 
RETURN 
CALCULATE( 
    COUNTROWS( Table3 ),  
    ALLSELECTED( Table3 ), 
    Table3[Product] = __currentProduct 
)

[mc2863]

attempting to use this solution, but the measure returns empty calculations. Is this measure possible with a table as a new measure column?  If I hard code the  value selection, the countrow works, but using selected value is just blank.

[Anonymous]

Thanks a lot @parry2k,

It did work... there were a couple changes I had to do to make it work for me as I'm connecting to SSAS compatibility level 1103 (no variables and selectedvalue function). The exepression that has worked for me is:

Count of Products = 
CALCULATE( 
    COUNTROWS( Table1 ),  
    ALLSELECTED( Table1 ), 
    Table1[Product] = IF ( 
                       HASONEVALUE ( Table1[Product] ), 
                       VALUES ( Table1[Product] ) 
                       )
)

Thanks again,

Lupa