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
ClemFandango
Helper II
Helper II

Dax conditional If statement for counting rows in date table

Hello all,

I am trying to use CountRows, but toiling a bit with a conditional if statement. I have the following date table called MMYYDD

MMYYDDCount
31/01/20183
28/02/20183
31/03/20183
29/11/20184
31/12/20186
31/01/201910
30/05/201914

 

ClemFandango_0-1683646966340.png

 

 

And another table called 'Summary'

MinDateMaxDateRejoinDateDateEnd
01-Jan-1830-Apr-2230/04/202129/04/2020
01-Jan-1830-Nov-2323/11/202130/03/2020
01-Jan-1831-May-2331/05/2021 
31-Jan-1928-Feb-2219/01/202131/01/2020
31-Jan-1931-Jan-2420/01/202331/01/2022
30-May-1931-May-22 31/05/2020
31-Jan-1929-Feb-2411/12/2020 
31-Dec-1831-Mar-2419/03/202131/12/2020
31-Dec-1831-Oct-2326/08/202031/12/2019
17-Apr-1930-Apr-2401/02/2023 
31-Jan-1931-Dec-2215/10/202030/09/2020
29-Nov-1831-Jan-24 30/11/2019
29-May-1930-Sep-2326/09/202231/05/2022
16-Dec-1930-Apr-2431/01/2021

18/12/2019

 

ClemFandango_1-1683647016222.png

 

The 'Count' column in the MMYYDD table is calculated as below:-

 

Count = 
COUNTROWS (
    FILTER (
        'Summary',
        [MMYYDD] >= 'Summary'[MinDate]
            && [MMYYDD] < 'Summary'[MaxDate]
    )
)

 

 

I would like to add a second Count to the MMYYDD table that counts both of the following conditions:-
Condition 1. [MMYYDD] >= 'Summary'[MinDate] && [MMYYDD] < 'Summary'[DateEnd]

Condition 2. [MMYYDD] >= 'Summary'[RejoinDate] && [MMYYDD] < 'Summary'[MaxDate]

 

However only count Condition 1 if 'Summary'[DateEnd] is not blank
and only count Condition 2 if 'Summary'[RejoinDate] is not blank

 

If condition 1 or condition 2 is blank then
[MMYYDD] >= 'Summary'[MinDate] && [MMYYDD] < 'Summary'[MaxDate]

 

Any ideas and massive thanks for anyone that can help!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

here's a general idea of how to implement that

 

ct2 =
VAR a =
    SUMMARIZE(
        MMYYDD,
        MMYYDD[MMYYDD],
        "ct",
            VAR d = [MMYYDD]
            RETURN
                CALCULATE(
                    COUNTROWS( Summary ),
                    COALESCE( Summary[RejoinDate], Summary[MinDate] ) <= d,
                    COALESCE( Summary[DateEnd], Summary[MaxDate] ) > d
                )
    )
RETURN
    SUMX( a, [ct] )

 

You can refine the logic by testing for both conditions together.

 

lbendlin_0-1683775526440.png

View solution in original post

2 REPLIES 2
ClemFandango
Helper II
Helper II

Great stuff, thanks @lbendlin 

lbendlin
Super User
Super User

here's a general idea of how to implement that

 

ct2 =
VAR a =
    SUMMARIZE(
        MMYYDD,
        MMYYDD[MMYYDD],
        "ct",
            VAR d = [MMYYDD]
            RETURN
                CALCULATE(
                    COUNTROWS( Summary ),
                    COALESCE( Summary[RejoinDate], Summary[MinDate] ) <= d,
                    COALESCE( Summary[DateEnd], Summary[MaxDate] ) > d
                )
    )
RETURN
    SUMX( a, [ct] )

 

You can refine the logic by testing for both conditions together.

 

lbendlin_0-1683775526440.png

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.

Top Solution Authors