why countrows works without the filter condition

[Anonymous]

Hello,

I have the below dax code. When in Data panel, the code returns a table. The problem is when I try to convert it to a measure, meaning to count the rows in the table and aggregate the count in a card. I noticed that if I exclude the FILTER condition, the countrows (table3) works, but when I add the FILTER it returns blank.

Note that even with the FILTER added, in the Data panel I can see the table, but when I try to add it in a measure it says that the value supplied is not a valid table expression.

What am I doing wrong?

var MaxDate = EOMONTH(SELECTEDVALUE('Calendar '[Month Year]),0)

var ActiveFirstQ =

CALCULATETABLE(

values(TABLE[data]),

DATESBETWEEN(TABLE_V2[data],maxdate-60,MaxDate))

var ActiveSecQ =

CALCULATETABLE(

values(TABLE[data]),

DATESBETWEEN(TABLE_V2[data],maxdate-150,MaxDate-61))

var ActiveThirdQ =

CALCULATETABLE(

values(TABLE[data]),

DATESBETWEEN(TABLE_V2[data],maxdate-240,MaxDate-151))

var ActiveFourthQ =

CALCULATETABLE(

values(TABLE[data]),

DATESBETWEEN(TABLE_V2[data],maxdate-330,MaxDate-241))

var table1 =

ADDCOLUMNS(

FILTER(

summarize(

TABLE_V2,

[user_id],

[data],

[tip_activitate]

),

not ISBLANK([user_id])

),

"FirstQ",

switch(TRUE(),

[data] in ActiveFirstQ,1,0),

"SecondQ",

switch(TRUE(),

[data] in ActiveSecQ,1,0),

"ThirdQ",

switch(TRUE(),

[data] in ActiveThirdQ,1,0),

"FourthQ",

switch(TRUE(),

[data] in ActiveFourthQ,1,0)

)

var table2 =

SUMMARIZE(

table1,

[user_id],

[FirstQ],

[SecondQ],

[ThirdQ],

[FourthQ],

"Add",

[FirstQ]+[SecondQ]+[ThirdQ]+[FourthQ])

var table3 =

//FILTER(

ADDCOLUMNS(

summarize(

table2,

[user_id]),

"@total",

sumx(

filter(

table2,

[user_id]=earlier([user_id])),

[Add]))

//,

// [@total]=4)

return

countrows(table3)

[Anonymous · Accepted solution]

Hi all,

What it needed in order to work is an ALL(Calendar[Month Year]) in the first 4 ActiveQ variables and an ALL (TABLE) in table1 var.

Thank you, all!

[v-stephen-msft]

Hi @Anonymous ,

You can create a calculated column to check whether the returned table is empty when [@total]=4.

Then enter the following formula, you can see this table in the Data view.

aa =
VAR MaxDate =
    EOMONTH ( SELECTEDVALUE ( 'Calendar '[Month Year] ), 0 )
VAR ActiveFirstQ =
    CALCULATETABLE (
        VALUES ( TABLE[data] ),
        DATESBETWEEN ( TABLE_V2[data], maxdate - 60, MaxDate )
    )
VAR ActiveSecQ =
    CALCULATETABLE (
        VALUES ( TABLE[data] ),
        DATESBETWEEN ( TABLE_V2[data], maxdate - 150, MaxDate - 61 )
    )
VAR ActiveThirdQ =
    CALCULATETABLE (
        VALUES ( TABLE[data] ),
        DATESBETWEEN ( TABLE_V2[data], maxdate - 240, MaxDate - 151 )
    )
VAR ActiveFourthQ =
    CALCULATETABLE (
        VALUES ( TABLE[data] ),
        DATESBETWEEN ( TABLE_V2[data], maxdate - 330, MaxDate - 241 )
    )
VAR table1 =
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE ( TABLE_V2, [user_id], [data], [tip_activitate] ),
            NOT ISBLANK ( [user_id] )
        ),
        "FirstQ", SWITCH ( TRUE (), [data] IN ActiveFirstQ, 1, 0 ),
        "SecondQ", SWITCH ( TRUE (), [data] IN ActiveSecQ, 1, 0 ),
        "ThirdQ", SWITCH ( TRUE (), [data] IN ActiveThirdQ, 1, 0 ),
        "FourthQ", SWITCH ( TRUE (), [data] IN ActiveFourthQ, 1, 0 )
    )
VAR table2 =
    SUMMARIZE (
        table1,
        [user_id],
        [FirstQ],
        [SecondQ],
        [ThirdQ],
        [FourthQ],
        "Add",
            [FirstQ] + [SecondQ] + [ThirdQ] + [FourthQ]
    )
VAR table3 =
    //FILTER(
    ADDCOLUMNS (
        SUMMARIZE ( table2, [user_id] ),
        "@total", SUMX ( FILTER ( table2, [user_id] = EARLIER ( [user_id] ) ), [Add] )
    ) //,
// [@total]=4)
RETURN
    table3

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.