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])
                              , DATEDIFF(ECO[CreateDate]
                              , NOW()
                              , DAY) <= Limiter ))
 Return RowCount           


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close