How to add an ad-hoc DATATABLE to a Power BI report (which is connected to a cube)?

[Anonymous]

I am using Power BI Desktop for Reporting Services Jan 2022.

The Data Source is an Analysis Services database.

The tabular model is used by dozens of reports.

In one report, I would like to create a new table to drive a slicer with some specific values.

Does anyone know how to do this?

I have tried creating a DAX measure DAX using the DATATABLE function to create this table, but that table cannot be used in a slicer.

[Anonymous · Accepted solution]

Hi @Anonymous ,

When you use a live connection to an Analysis Services database, new tables cannot be created on the desktop. Please consider going to SSAS to create a new table.

Best Regards,
Community Support Team Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

That's a shame. This will be a report-specific table, so does not belong in the cube, only this one report.

You can create measures in specific reports, so I don't see why you shouldn't be able to create tables in specific reports as well.

[Anonymous]

Hi @Anonymous ,

This is the limitation of the connection mode.

You can create report level measures in the Power BI Desktop, but all other query and modeling features are disabled when exploring live data.

The preview function can now be turned on from the desktop and the live connection can be switched to direct query mode. This allows you to create new fields or tables in mixed mode. Maybe this feature will be turned on in the report server as well, which will solve this problem.

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

Best Regards,
Gao
Community Support Team

[amitchandak]

@Anonymous , If that is live connection, I doubt you can create a table.

A table can not take slicer value, so you can not have a table that gets filtered with slicer

You can have temporary table in measure 

example

min Version_Id =
var _tab = union(all('Table'[Version_Id1]),all('Table'[Version_Id2]),all('Table'[Version_Id3]),all('Table'[Version_Id4]),all('Table'[Version_Id5]))
Return
minx(_tab,[Version_Id1])

measure =
var _tab = summarize('Table'[Bill No], "_Version_Id",firstnonbank('Table'[Submitted Version_Id]),"_qty",sum('Table'[Qty])
)
return
divide(sumx(_tab,if(not(isblank[_Version_Id])),[_qty],0),sumx(_tab,[_qty]))