Dax conditional If statement for counting rows in date table

ClemFandango — Tue, 09 May 2023 15:53:59 GMT

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

MMYYDD	Count
31/01/2018	3
28/02/2018	3
31/03/2018	3
29/11/2018	4
31/12/2018	6
31/01/2019	10
30/05/2019	14

And another table called 'Summary'

MinDate	MaxDate	RejoinDate	DateEnd
01-Jan-18	30-Apr-22	30/04/2021	29/04/2020
01-Jan-18	30-Nov-23	23/11/2021	30/03/2020
01-Jan-18	31-May-23	31/05/2021
31-Jan-19	28-Feb-22	19/01/2021	31/01/2020
31-Jan-19	31-Jan-24	20/01/2023	31/01/2022
30-May-19	31-May-22		31/05/2020
31-Jan-19	29-Feb-24	11/12/2020
31-Dec-18	31-Mar-24	19/03/2021	31/12/2020
31-Dec-18	31-Oct-23	26/08/2020	31/12/2019
17-Apr-19	30-Apr-24	01/02/2023
31-Jan-19	31-Dec-22	15/10/2020	30/09/2020
29-Nov-18	31-Jan-24		30/11/2019
29-May-19	30-Sep-23	26/09/2022	31/05/2022
16-Dec-19	30-Apr-24	31/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!

Re: Dax conditional If statement for counting rows in date table

lbendlin — Thu, 11 May 2023 03:25:39 GMT

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.

Re: Dax conditional If statement for counting rows in date table

ClemFandango — Thu, 11 May 2023 13:23:24 GMT

Great stuff, thanks lbendlin

topic Re: Dax conditional If statement for counting rows in date table in DAX Commands and Tips

Dax conditional If statement for counting rows in date table

Re: Dax conditional If statement for counting rows in date table

Re: Dax conditional If statement for counting rows in date table