Forum Discussion

joaodd's avatar
joaodd
Frequent Visitor
3 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
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • joaodd's avatar
    joaodd
    Frequent Visitor
    3 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])))