Forum Discussion
Variable Tables
Hi, I've written the below measure, that when evaulated against the unique ID provides me with the correct values I'm looking for. EYFS_GLD_KeyAreas_Count = CALCULATE( COUNT(TR_Pupil_EYFS[Un...
Thank you for you help. 🙂
I've played around a bit more and got the solution. Here is my final code
Thanks Paul,
But that just gives me the total number (so all the 19's, 18's etc added together). I think because my measure hasn't got any context in it, I only get the number split when the Unie id is added in. Thats why I'm a bt stuck.
In SQL, the above would just form an inner query / temp table that I would just query further.
I'm struggling to replicate this in DAX.
Thanks
Ann
Anonymous
Oh I see. You want the list of ID with counts >= 19.
Ok try this:
EYFS_GLD_KeyAreas_Count =
VAR Calc =
CALCULATE (
COUNT ( TR_Pupil_EYFS[Unique Identifier] ),
TR_Pupil_EYFS,
EYFS_Typicality[Typicality] IN { "T", "AT" },
NOT ( TR_Pupil_EYFS[Development Area]
IN {
"People & Communities",
"The World",
"Technology",
"Exploring & Using Media & Materials",
"Being Imaginative"
} )
)
RETURN
COUNTROWS (
CALCULATETABLE (
VALUES ( TR_Pupil_EYFS[Unique Identifier] ),
FILTER ( 'TR_Pupil_EYFS', Calc >= 19 )
)
)
and add the measure to the visual, or to the "filters on this visual" in the filter pane (setting the value to 1).
If you want the IDs as a a single output:
EYFS_GLD_KeyAreas_Count =
VAR Calc =
CALCULATE (
COUNT ( TR_Pupil_EYFS[Unique Identifier] ),
TR_Pupil_EYFS,
EYFS_Typicality[Typicality] IN { "T", "AT" },
NOT ( TR_Pupil_EYFS[Development Area]
IN {
"People & Communities",
"The World",
"Technology",
"Exploring & Using Media & Materials",
"Being Imaginative"
} )
)
RETURN
CONCATENATEX (
CALCULATETABLE (
VALUES ( TR_Pupil_EYFS[Unique Identifier] ),
FILTER ( 'TR_Pupil_EYFS', Calc >= 19 )
),
TR_Pupil_EYFS[Unique Identifier],
", "
)