Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

why countrows works without the filter condition

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)
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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!