Forum Discussion

dasrotrad's avatar
dasrotrad
Frequent Visitor
8 years ago
Solved

DAX COUNT/COUNTA filtered by columns

I have two dates

 

PK        StartDate               EndDate

1         1/1/2000                1/2/2001

2         1/3/2000                1/2/2002

3         3/2/2001                1/1/2002

4         3/30/2001              3/1/2003

5         4/2/2001                3/1/2003

 

 

CountStartDate= CountA([StartDate])

CountEndDate=  CountA([EndDate])

 

Expected Result 

            CountStartDate     CountEndDate

2000              2                            0

2001              3                            1

2002              0                            2

2003              0                            3

 

 

 

Actual Result 

            CountStartDate     CountEndDate

2000              2                            2

2001              3                            3

2002              0                            0

2003              0                            0

 

I do not understand how this happens.  CountA is counting dates that do not occur in Column CountEndDate.

It returns the dates in CountStartDate column.

 

Thank you 

 

Robert

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi dasrotrad,

     

    You could create a calculated table with the formula below to get your desired output.

     

    Table =
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[StartDate], 4 ) ),
            SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[EndDate], 4 ) )
        )
    ),
    "count start", CALCULATE (
        COUNT ( Table1[StartDate] ),
        RIGHT ( Table1[StartDate], 4 ) = EARLIER ( [Year] )
    )
        + 0,
    "count end", CALCULATE (
        COUNT ( Table1[EndDate] ),
        RIGHT ( Table1[EndDate], 4 ) = EARLIER ( [Year] )
    )
        + 0
)

    Here is the output.

     

    Hope this can help you!

     

    Best  Regards,

    Cherry

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi dasrotrad,

     

    If it is convenient, could you share the screenshots of your error when you create the calculated table with the formula I provided in the first reply?

     

    In addition, you could have a reference of my test file which has been attached below.

     

    Best Regards,

    Cherry

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi dasrotrad,

     

    You could create a calculated table with the formula below to get your desired output.

     

    Table =
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[StartDate], 4 ) ),
            SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[EndDate], 4 ) )
        )
    ),
    "count start", CALCULATE (
        COUNT ( Table1[StartDate] ),
        RIGHT ( Table1[StartDate], 4 ) = EARLIER ( [Year] )
    )
        + 0,
    "count end", CALCULATE (
        COUNT ( Table1[EndDate] ),
        RIGHT ( Table1[EndDate], 4 ) = EARLIER ( [Year] )
    )
        + 0
)

    Here is the output.

     

    Hope this can help you!

     

    Best  Regards,

    Cherry

    • dasrotrad's avatar
      dasrotrad
      Frequent Visitor
      8 years ago

      Thank you Cherry.  When I type the Table formula, the code accepts Table1[StartDate], but when I get to the first instance of  Table1[EndDate], the Table1[EndDate] gets the red underline and intelisense quits.  Everything subseqeunt becomes undelined with the squigley red line.

       

      Robert

       

       

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        Hi dasrotrad,

         

        If it is convenient, could you share the screenshots of your error when you create the calculated table with the formula I provided in the first reply?

         

        In addition, you could have a reference of my test file which has been attached below.

         

        Best Regards,

        Cherry