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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    joaodd
    Frequent Visitor
    2 years ago

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