Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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[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"}) )
 
aguest1005_0-1616596373993.png

 

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for you help. 🙂

I've played around a bit more and got the solution. Here is my final code

 

aguest1005_0-1616685540086.png

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Thank you for you help. 🙂

I've played around a bit more and got the solution. Here is my final code

 

aguest1005_0-1616685540086.png

 

PaulDBrown
Community Champion
Community Champion

@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 )




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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],
        ", "
    )

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hi,

Would you be able to help with that. I'm really struggling.

Thanks
Ann

selimovd
Super User
Super User

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 regards
Denis
 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.