Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Count of Rows

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 ...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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 
)
parry2k's avatar
parry2k
Super User
7 years ago

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 
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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