Forum Discussion

Shota_Xuc's avatar
Shota_Xuc
Helper I
2 years ago

Count rows with same value in one column

Hello,   I have a column "Valuation date" with dates like: 2024-02-06 2024-02-06 2024-02-06 2024-02-07 2024-02-07 2024-02-07 2024-02-09 2024-02-10 2024-02-10 2024-02-12 2024-02-12 2024-...
"Need Help"
Shota_Xuc's avatar
Shota_Xuc
Helper I
2 years ago

Hi NickDanger, it does not work. E.g. I should get 3 for 02-06, 1 for 02-09, 2 for 02-13 instead.

I think the expression takes numbers from ground table my visual is based on, but rows in my visual are filtered out. I need to count rows with the same valuation date on my visual below.

 

NickDanger's avatar
NickDanger
Regular Visitor
2 years ago

Shota_Xuc 
Thanks for the elaboration. The original post did have much info. Here is a code snippest of test data from AdventureWorks that I mocked up that would work with your issue.

Test Measure = 
var _SelectedValueDate = SELECTEDVALUE('FactInternetSales'[OrderDate])
RETURN
CALCULATE(
    COUNTROWS('FactInternetSales'),
    ALLEXCEPT('FactInternetSales', 'FactInternetSales'[PromotionKey]), //Add any columns here that you're filtering on
    'FactInternetSales'[OrderDate] = _SelectedValueDate)

Include any of the columns you're filtering your table on in the ALLEXCEPT function. For instance, in my data I filtered on PromotionKey. 
This first screenshot is without including PromotionKey in the ALLEXCEPT, the second screenshot is when it's written to account for the filter.

 

 

  • Shota_Xuc's avatar
    Shota_Xuc
    Helper I
    2 years ago

    NickDanger 

    it does not work, I have adjusted your code to my visual, here is my code:

    Test Measure =
    var _SelectedValueDate = SELECTEDVALUE('SFX_TRS_RESET_MATURITY_DATES'[Valuation date])
    RETURN
    CALCULATE(
        COUNTROWS('SFX_TRS_RESET_MATURITY_DATES'),
        ALL('SFX_TRS_RESET_MATURITY_DATES'), //Add any columns here that you're filtering on
        'SFX_TRS_RESET_MATURITY_DATES'[Valuation date] = _SelectedValueDate)
    and I get wrong numbers:

     

     
    • NickDanger's avatar
      NickDanger
      Regular Visitor
      2 years ago

      Sorry if there was confusion, but your code does not match mine. You need to use 

      ALLEXCEPT('SFX_TRS_RESET_MATURITY_DATES', 'SFX_TRS_RESET_MATURITY_DATES'[FILTERED COLUMN HERE]])


      Not ALL('SFX_TRS_RESET_MATURITY_DATES')

      If you're filtering on more than one column, separate them with a comma after each column listed. 

      • Shota_Xuc's avatar
        Shota_Xuc
        Helper I
        2 years ago

        NickDanger 

        I am not filtering on more than one column, 

        I tried the below code, but I get wrong numbers:

         

        Test Measure =
        var _SelectedValueDate = SELECTEDVALUE('SFX_TRS_RESET_MATURITY_DATES'[Valuation date])
        RETURN
        CALCULATE(
            COUNTROWS('SFX_TRS_RESET_MATURITY_DATES'),
            ALLEXCEPT('SFX_TRS_RESET_MATURITY_DATES', SFX_TRS_RESET_MATURITY_DATES[Valuation date]), //Add any columns here that you're filtering on
            'SFX_TRS_RESET_MATURITY_DATES'[Valuation date] = _SelectedValueDate)