Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Thank you for you help. 🙂
I've played around a bit more and got the solution. Here is my final code
Thank you for you help. 🙂
I've played around a bit more and got the solution. Here is my final code
@Anonymous
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 )
Proud to be a Super User!
Paul on Linkedin.
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],
", "
)
Proud to be a Super User!
Paul on Linkedin.
Hi,
Would you be able to help with that. I'm really struggling.
Thanks
Ann
HEy @Anonymous ,
you can reach that with the FILTER function.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |