Using Parameters on PBI desktop with a Live Connection to SSAS Tabular

[rax99]

Hello everyone,

I can't seem to find an answer to my query so cannot be sure if it has been asked before but I apologize if it has. 

So I have started to use a live connection to an SSAS tabular server for my PBI reports. The idea being, its a centralized source, and would offer better performance.

One of the key features many of our end users use is the input parameter function (basically enabling the report to filter/update based on an arbitrary value input into the parameter box). Unfortunately with the live connection, the parameter button has been greyed out. Anyone know of a workaround or fix for this? How else can I achieve the same function as a parameter input box?

see below:

showing live connection

[Anonymous]

You can add different column in slicers and ask users to select value in slicer dropdown. It will work fine.

We are doing the same thing in our project.

[rax99]

Hi @Anonymous ,

Thanks for the advice. Do you have a quick demo of this? Will the slicer have to be of pre-existing data in your tables?

[Anonymous]

Whatever data is there in your cube it will get reflected in  your POWER BI file.

And one more think you can not create column or tables in Power BI if source is live connection. You to do all thing in Cube itself.

[rax99]

Thanks for your input. The only issue with this method is that the data has to already be available/stored in the cube somewhere. What I need is a parameter where I can enter an arbitrary number such as '24', and I would want a measure on the page to filter the page based on the SELECTEDVALUE in the parameter box. Can such a thing be replicated via a live connection?

[Anonymous · Accepted solution]

You can use dummy table in cube, means let's say you want to take input from number, based on your requirement you can create one dummy table having one column with number series like 1 to 100 or anything.

Create table in model and don't make any relationship with that table.

Now use that column in slicer and write measure.

Let's say in your fact you have one measure and you want to display measure only if it is greater than selectedValue.

So create measure at power BI level/Cube level, it works fine in both condition.

Measure=if(Sum(Fact[amount])> Selectedvalue(Dummy[Series]),1,0)

Add measure in visual level filter and set measure is 1.

For data inputs as well you can create one data dummy table in cube and used it as slicer.

Calculated table as

Date Table =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))

[rax99]

Thanks for this answer @Anonymous .

However, I'm having problems accessing the SELECTEDDVALUE function, it doesn't appear to be valid on a live connection:

list of available 'SELECTED..' functions in the cube and report:

CUBEPBI

Could this be due to versions? Do you get the SELECTEDVALUE function appearing in your cube/report measures?

Also, last question, do you know how to generate a dummy table with values say 1 -100?  in PBI you can use generateseries function but can't seem to find a similar option in SSAS.

Thanks for all your help thus far, its really been appreciated.

[Anonymous]

SelectedValue and generateseries both functions work in Power BI as well as In Tabular model. Please check case sensitivity at model level.

[rax99]

@Anonymous 

Its strange now that I have that option available in the Cube but still doesnt appear in PBI. When I now deploy the dummy table GENERATESERIES(1,100,1)

and add the value to a slicer I get the following error:

Any ideas? Also, I checked the collation setting in the SQL server (where i have imported my data from) and that's showing as CI

[Anonymous]

Create one calculated table in model with below syntax

GENERATESERIES(1, 100).

And first check values at model level only in model or by browsing cube from ssms.

[rax99]

So I have created the table in the cube, but notice that when it deploys it doesn't seem to deploy any of the rows:

Also in SSMS its showing as this error:

Any ideas?

[Anonymous]

refrest that calculated table once and check again. Becuase same thing is working in my case. Make sure this table is not connected with other tables.

[Anonymous]

in my case dummy calculated tables are not showing when i am processing model. why  is it showing in your case?

[Anonymous]

Just now i have rechecked, first do "process all" and then deploy model to analysis services.

[rax99]

Yes sorry so just to clarify. When 'Processing All', its only processing real tables. When deploying to services, it deployed all tables including dummy tables. But for some reason, the dummy table is showing as 0 rows transferred in the deploying status box, and not showing any errors. I've just tried again and it's still showing the same error in SSMS also.

Is this because I'm running on 2016 RTM?

[Anonymous]

It is released in july 2017 update so plz check your version.

[Anonymous]

if it solves your problem please mark it as solution