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