How to include a dinamic variable into a temporary column

[joaodd]

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 table. For any given date, the valid status for each student is the last status registered on this table

I need to calculate the number of students with a specific status "ACTIVE", considering any date 'Calendar'[Date] defined by the Power BI user using a slicer on a dashboard.

For this, i thought of creating a temporary table with the latest status for each StudentNumber, and then count the rows with Status = "ACTIVE".

I used this DAX code:
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]

    ))

But this temporary table doesn't accept the selectedvalue('Calendar'[Date]) as defined by the user, it will not work. Where am I getting this wrong? I also tried to do a COUNTROW on this temporary table, but Power Bi doesn't accept a summarizecolumn 

[Anonymous]

Hi  @joaodd ,

I created some data:

Are you referring to using a slicer to filter the calculated table?

As far as I know, Power BI can't implement this requirement for the time being, the slicer doesn't affect the calculated table, the calculated columns and the calculated table are calculated once when the data is loaded for the first time, and it can't directly calculate the data in the whole table either, you can consider to create a measure+IF() function to implement it in Visual.

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Calendar'[Date])
var _maxdate=
MAXX(
    FILTER(ALL(REGISTRATION),
    'REGISTRATION'[Date]<_select &&'REGISTRATION'[Studentnumber]=MAX('REGISTRATION'[Studentnumber])),[Date])
return
IF(
    MAX('REGISTRATION'[Date]) =_maxdate,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[joaodd]

Thanks for your input. My issue is that i'm not trying to create a calculated table but rather a temporary or virtual table within a measure, and still I cannot get the value from the date slicer to allow the calculation of that measure. 
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]

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-...
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/1447...

[joaodd]

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

[joaodd]

Here's the link: https://transfer-vinci-energies.netexplorer.pro/dl/RY8c3ksKxYBsqMupb3xxHTSvLBulUP

I tried your suggestion but PowerBI retrieves the following error:
"It's not possible to find the table Active Registrations".

[lbendlin]

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]

Here's the link: https://transfer-vinci-energies.netexplorer.pro/dl/RY8c3ksKxYBsqMupb3xxHTSvLBulUP

I tried your suggestion but PowerBI retrieves the following error:
"It's not possible to find the table Active Registrations".

[lbendlin]

Now, granted, this is not very fast. But it's something you can start from.

[joaodd]

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  

[lbendlin]

yes, that's what my proposal is doing - number of active students on a given day.