Forum Discussion

ClemFandango's avatar
ClemFandango
Advocate II
3 years ago
Solved

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

 

 

 

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

 

 

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!

"Calculated Column"
COUNT
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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.