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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ClemFandango
Helper III
Helper III

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 III
Helper III

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.