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.
However, what I really want as an outcome is the number of Unique IDs that have the count >=19
I don't know how to progress this. I can't seem to get the syntaxt for a temp / variable table correct.
is tehre an easier way to achieve what I want to achieve ?
TIA
Ann
6 Replies
HEy Anonymous ,
you can reach that with the FILTER function.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biAnonymous
Try:
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 IF ( Calc >= 19, Calc )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.
ThanksAnn
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], ", " )
