Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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!
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!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |