Dynamic filters in PowerBI/SSAS

For a while i’ve been struggling to implement a pattern on PowerBI on a SSAS Cube. I wanted to allow my users to dynamically make range sliders on a value.

I finally succeed and this is how I implemented the solution.

1/ In the cube create a numbers table in my example i am calling this table “Counter Model Wide“, for now this is done by

SELECTCOLUMNS ( CALENDAR ( 0, 100000 ), “Value”, INT ( [Date] ) )
But soon it will be possible todo this though a special purpose statement GENERATESERIES(0; 100000; 1)

2/ Create a measure on the entity in PowerBI, the filter value causes the statement to be evaluated in a row context. Remember this is done Row by agonizing Row, and can easily become to much when dealing with larger sets of data.

Count of CreateDate Limited = 
VAR Limiter=MAX('Counter Model Wide'[Value])
VAR RowCount=CALCULATE(COUNTROWS(ECO),  
                       FILTER(ECO  
                              , DATEDIFF(ECO[CreateDate]
                              , NOW()
                              , DAY) <= Limiter ))
 Return RowCount