Forum Discussion

joaodd's avatar
joaodd
Frequent Visitor
2 years ago

How to include a dinamic variable into a temporary column

I have a table "REGISTRATION" with 3 fields: - StudentNumber - Date - Status This table registers any change in the student status, each time the student status changes it is registered in this ...
Need Help
joaodd's avatar
joaodd
Frequent Visitor
2 years ago

Please find a sample data: https://transfer-vinci-energies.netexplorer.pro/fdl/eSj2RoLNz_SPHwUux61wQwModc54Vv

Please note that I don't want to create a calculated table, but rather a measure that can count how many active students are present on any date selected by the user using a slicer. So i'm considering this temporary table inside a measure, something like this:

Active Students =
VAR 

Active Registrations = SUMMARIZECOLUMNS(
    'REGISTRATION'[StudentNumber],
    FILTER('REGISTRATION', 'REGISTRATION'[Date] < selectedvalue('Calendar'[Date]),
    "MostRecentDate",
    MAXX(
        TOPN(
            1,
            filter('REGISTRATION','REGISTRATION'[Date] <= selectedvalue('Calendar'[Date])),
            'REGISTRATION'[Date],        
            DESC
        ),
        'REGISTRATION'[Date]
    ),
    "MostRecentStatus",
    MAXX(
        TOPN(
            1,
            filter('REGISTRATION','REGISTRATION'[Date] <= selectedvalue('Calendar'[Date])),
            'REGISTRATION'[Date],
            DESC
        ),
        'REGISTRATION'[Status]
    ))
RETURN Countrows(summarizecolumns('Active Registrations',filter('Active Registrations'[Status] = "ACTIVE])))



lbendlin's avatar
lbendlin
Super User
2 years ago

You need to protect SELECTEDVALUE from FILTER.  Move it out into a variable.

 

Active Students = 

var sd = selectedvalue('Calendar'[Date])

...

FILTER('REGISTRATION', 'REGISTRATION'[Date] < sd,

...

 

etc

 

Can you please re-enable the download?

      • joaodd's avatar
        joaodd
        Frequent Visitor
        2 years ago

        Hi lbendlin , thanks for your inputs.

        The key objective is not to know how many students registrations switched to ACTIVE on each day, but rather how many have the status ACTIVE on a given date.

        Thanks