Create temp table or array

[Toni]

Hi,

Am wondering, can we create a measure contain a dynamic table and this table is contain a made up data as well ?

The intentions is to have a slicers that contain of those rows in the temp table. So I would like to have a slicer that contain items like : Option1 and Option2, that’s all.

And I’m in Direct Query, any workaround how to do it ? Basically those selections :Option1 and Option2 is not in any of my table or data. It is really a made up data.

Thanks

[BrianJ]

Toni,

As I understand your question, I believe the answer is no. The only tables in Power BI that are fully dynamic are virtual tables, and virtual tables exist only within measures. However, measures can’t be used in slicers, so I think what you’re requesting is currently impossible in PBI.

However, there are lots of clever, kluidgy workarounds to this limitation, so if you’re able to provide a specific example within a PBIX file, we may be able to come up with an approach that fully or mostly meets your requirements.

I hope that’s helpful.

• Brian

[AntrikshSharma]

@BrianJ There is one way but it is only applicable in SSAS as of now, Using DETAILROWS functions you can actually create a measure that returns table and it can be used like this:

CALCULATE ( [Measure], DETAILROWS ( [Table Measure] )

[Toni]

Hi @BrianJ,

Thanks. for the sample PBIX, it is actually quite straight forward. The Option1 and Option2 in that slicer I’m gonna use it to switch my bookmark. So I have two tables that display interchangeably by those Options, so when I choose Option1, table A will show, when I choose Option2, table 2 will show.

I can use button, but I want the effect of button pressed, which the normal button not support it. Also I can create 2 button and make it hidden one at a time, but that also means user may not know what is the options available.

I can attach PBIX, but just wondering will that help coz I’m actually using Direct Query. If it is Import mode, I can easy create table and enter the options manually.

Anyway here it is.SampleSwitchBookmark.pbix (16.9 KB)
In that sample there are 2 table visual with different fields displayed. I’m using table to switch between those 2 table visual. But what I want is the button will stay pressed which the normal button cannot do that.

Thanks,

[BrianJ]

@AntrikshSharma

,

Wow – learn something new every day. From that measure how would you go about selecting the column for the slicer?

• Brian

[BrianJ]

@Toni,

Take a look at this thread:

Adding a slicer based on Measure

@Haitham, Welcome to the forum – great to have you here! Power BI doesn’t support slicing based on measures, only columns. While there are some clever workarounds to that restriction, I don’t think they will be useful here. Instead, what I would do is to create a “pseudo-slicer” based on buttons linked to page navigation actions. You can also do this using bookmarks, but I find the former approach both more flexible, and easier to implement and maintain. Basically, what you want to do is cre…

and the Publish To Web version I posted:

Power BI Report

I think with minor modification this may meet your requirement. However, I’d have to look at whether Direct Query throws a wrench into this, since I work almost exclusively in import mode…

• Brian

[AntrikshSharma]

@BrianJ I haven’t tried DETAILROWS enough to confirm, will have to try and identify what else can we achieve with that.

[EnterpriseDNA]

Hi @Toni, did the response provided by @AntrikshSharma and @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!