Variable Tables

[Anonymous]

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"}) )

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

[Anonymous · Accepted solution]

Thank you for you help. 🙂

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

[PaulDBrown]

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

[Anonymous]

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

[PaulDBrown]

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

[Anonymous]

Hi,

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

Thanks
Ann

[selimovd]

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

Blog: WhatTheFact.bi