Forum Discussion
Using Parameters on PBI desktop with a Live Connection to SSAS Tabular
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))
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))
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.
SelectedValue and generateseries both functions work in Power BI as well as In Tabular model. Please check case sensitivity at model level.
It is released in july 2017 update so plz check your version.
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
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.