Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Aromeo
New Member

Calculated Table working dynamically

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

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
OzkanDhont
Resolver II
Resolver II

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

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
Super User
Super User

@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.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.