Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |