Calculated Table working dynamically

[Aromeo]

Good morning,
I would like the following table calculated:

  OFFICES ON MAP = CALCULATETABLE (VALUES ('OFFICE LIST' [OFFICE_DEST]), 'OFFICE LIST' [OFFICE_ORIG] = "OFFICE NAME")

would work dynamically, i.e. instead of the string "OFFICE NAME" there was the value selected by a drop-down filter

The "static" version works correctly, but when I try to use the OFFICE FILTER measure instead of "OFFICE NAME", defined as follows:

  OFFICE FILTER = SELECTEDVALUE ('OFFICE LIST' [OFFICE_ORIG])

gives me the following error:
[i] [u] A 'PLACEHOLDER' function was used in a True / False expression used as a table filter expression. This is not allowed. [/ U] [/ i]

Thanks in advance for your attention, greetings

Alessio Romeo

[Anonymous]

Hi @Aromeo,

Current power bi does not support creating dynamic calculated columns/tables based on filter effects. They were working on different levels and you can't use the child to effet its parent level.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[OzkanDhont]

Hi @Aromeo !

Try using the filter function:

CALCULATETABLE (VALUES ('OFFICE LIST' [OFFICE_DEST]), FILTER('OFFICE LIST' [OFFICE_ORIG] = [Measure]))

ps: A calculated Table is calculated when the datamodel is refreshed. It won't update when you only use the slicer.

Hope it helps!

Kind regards,

OD

[mkj1213]

Hi @OzkanDhont 
What if the connection to OFFICE LIST table was direct query and not import? will your solution using the filter function work? Will the calculatetable expression be sent as an SQL query : SELECT * FROM office_list where office_ORIG = 'Measurevalue' to the database engine? 

[Aromeo]

Hi @OzkanDhont,

tried and no errors occur, but the Filter is not working (I get all the OFFICE_ORIG in the calculate table), even if I refresh. Other Suggestions?

Thanks, regards

Alessio Romeo

[Fowmy]

@Aromeo 

It is not possible to pass dynamic values into Tables when you create them at model level. Model and the Reporting canvas (Visualization) are two different layers. When you build the tables, then they get loaded on the model and any slicer or filter selection from the canvas would not influence the model.

Based on your requirment, you make think of creating a virtual table within the measure.