How to include a dinamic variable into a temporary column

Question

I have a table "REGISTRATION" with 3 fields:- StudentNumber- Date- StatusThis 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 tableI 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(&nbsp; &nbsp; 'REGISTRATION'[StudentNumber],&nbsp; &nbsp; FILTER('REGISTRATION', 'REGISTRATION'[Date] &lt; selectedvalue('Calendar'[Date]),&nbsp; &nbsp; "MostRecentDate",&nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filter('REGISTRATION','REGISTRATION'[Date] &lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'REGISTRATION'[Date], &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'REGISTRATION'[Date]&nbsp; &nbsp; ),&nbsp; &nbsp; "MostRecentStatus",&nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filter('REGISTRATION','REGISTRATION'[Date] &lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'REGISTRATION'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; 'REGISTRATION'[Status]&nbsp; &nbsp; ))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&nbsp;

lbendlin · Answer

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

anonymous · Answer

Hi&nbsp; joaodd&nbsp;,
I created some data:
&nbsp;
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.

&nbsp;
Here are the steps you can follow：
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Calendar'[Date])
var _maxdate=
MAXX(
    FILTER(ALL(REGISTRATION),
    'REGISTRATION'[Date]&lt;_select &amp;&amp;'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:

&nbsp;
&nbsp;
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 · Answer

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.&nbsp;Active Students =VAR&nbsp;Active Registrations =&nbsp;SUMMARIZECOLUMNS(&nbsp; &nbsp;&nbsp;'REGISTRATION'[StudentNumber],&nbsp; &nbsp;&nbsp;FILTER('REGISTRATION',&nbsp;'REGISTRATION'[Date]&nbsp;&lt; selectedvalue('Calendar'[Date]),&nbsp; &nbsp;&nbsp;"MostRecentDate",&nbsp; &nbsp;&nbsp;MAXX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;filter('REGISTRATION','REGISTRATION'[Date]&nbsp;&lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date], &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date]&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;"MostRecentStatus",&nbsp; &nbsp;&nbsp;MAXX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;filter('REGISTRATION','REGISTRATION'[Date]&nbsp;&lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Status]&nbsp; &nbsp; ))RETURN Countrows(summarizecolumns('Active Registrations',filter('Active Registrations'[Status] = "ACTIVE])

joaodd · Answer

Please find a sample data:&nbsp;https://transfer-vinci-energies.netexplorer.pro/fdl/eSj2RoLNz_SPHwUux61wQwModc54VvPlease 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&nbsp;Active Registrations =&nbsp;SUMMARIZECOLUMNS(&nbsp; &nbsp;&nbsp;'REGISTRATION'[StudentNumber],&nbsp; &nbsp;&nbsp;FILTER('REGISTRATION',&nbsp;'REGISTRATION'[Date]&nbsp;&lt; selectedvalue('Calendar'[Date]),&nbsp; &nbsp;&nbsp;"MostRecentDate",&nbsp; &nbsp;&nbsp;MAXX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;filter('REGISTRATION','REGISTRATION'[Date]&nbsp;&lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date], &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date]&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;"MostRecentStatus",&nbsp; &nbsp;&nbsp;MAXX(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;filter('REGISTRATION','REGISTRATION'[Date]&nbsp;&lt;= selectedvalue('Calendar'[Date])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESC&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'REGISTRATION'[Status]&nbsp; &nbsp; ))RETURN Countrows(summarizecolumns('Active Registrations',filter('Active Registrations'[Status] = "ACTIVE])))

lbendlin · Answer

You need to protect SELECTEDVALUE from FILTER.&nbsp; Move it out into a variable.
&nbsp;
Active Students =&nbsp;
var sd =&nbsp;selectedvalue('Calendar'[Date])
...
FILTER('REGISTRATION',&nbsp;'REGISTRATION'[Date]&nbsp;&lt; sd,
...
&nbsp;
etc
&nbsp;
Can you please re-enable the download?

Forum Discussion

How to include a dinamic variable into a temporary column

10 Replies

Related content

Get values from a temporary table variable

unable to query Temporary tables

Using a temporary table variable to simplify CALCULATE

Sorting the temporary table

Temporary view in a different cell