Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dasrotrad
Frequent Visitor

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

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

Hope this can help you!

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

Hope this can help you!

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.