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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Jaya
Frequent Visitor

slicer on report does not refresh calculated table

Hi,

 

I have a column named "Program" in both source table and calculated table. I added graphs for both source table and calculated table on my report along with a slicer. The slicer does not filter the calculated table graph. I read that calculated tables do not respond to slicers on the report. Any ideas on how I could make this work. 

 

Note: I couldn't create a relationship between the source table and the calculated table because it said - you can't create a relationship between these two columns because one of the columns must be unique values.

 

Thanks so much in advance!

1 ACCEPTED SOLUTION

Yeh, calculated tables, as in Modeling/New Table/Type stuff in dax, is a "static" table.  It will not respond to filters.

 

The error you mentined is not relevant in terms of the solution but, it is just telling you that niether of the two columns you are trying to join on has unique (non-repeated) values.

 

The solution, if you want your table to respond to filters, is to include the Calculate Table function in the target Measures.

View solution in original post

11 REPLIES 11
danextian
Super User
Super User

As far as I know, DAX created tables are static and do not respond to slicers from the original table and only update upon refresh. I had the same question during my early DAX days.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

There must be a way in DAX to get the values from the filters or the slicers then perform whatever the calculations may be accordingly.  Wizards are not there always.   That's what I am trying to find.

one way is to create a measure in a fact table or a pivot table that gets a SELECTEDVALUE from a slicer or table, then apply that measure in a filtered context DAX, whether implicit or explicit.

BalaVenuGopal
Resolver I
Resolver I

Hi @Jaya ,

 

As this is throwing error you cant create relationship because one of the columns must be unique values

Solution ;

 

1) Go to edit queries 

2) Select your first table and select append queries (Home tab right corner) then choose your second table

3) Select the join column from both the tables and remove all columns(Make sure your join column name is same n both tables)

4) Then remove duplicates from (Home --> Remove Rows---> Remove duplicates)

Use this table to join with your two tables use this column in report and drag remaining columns from  the tables.(Use newly creted tabel column in slicer too.

 

 

Plesae mark as solution if this works for you !!!!!!!!!!!!!!!!!!!

Thank you Smiley Happy

Hi @BalaVenuGopal,

 

Thank you so much for looking into it. I do not see my calculated table under the edit queries screen because I created the table using DAX. Is there anyway this could be achieved using DAX.

 

Thanks again!

Hi @Jaya,

 

Could you pls expain why did u create calculated table and give me table informaton along with your exact requirement 

 

thanks

Hi @BalaVenuGopal

 

I have the source table with a student start date and end date. I need to calculate how many days the student was present each month. So I created calculated columns for each student each month in the source table. Then I wanted to do a line graph that shows totals by month. But since the months are individual columns within the table I couldn't build a line graph. So I created a calculated table that computes the totals for each month. 

 

Calculated table has monthname, totaldays in that month, program

 

But now am unable to filter the line graph so it filters by program.

 

Hope I did it right! Thanks.

Yeh, calculated tables, as in Modeling/New Table/Type stuff in dax, is a "static" table.  It will not respond to filters.

 

The error you mentined is not relevant in terms of the solution but, it is just telling you that niether of the two columns you are trying to join on has unique (non-repeated) values.

 

The solution, if you want your table to respond to filters, is to include the Calculate Table function in the target Measures.


@RobertSlattery wrote:

 

The solution, if you want your table to respond to filters, is to include the Calculate Table function in the target Measures.


Sorry I just found this, and I'm having the same difficulties, could you elaborate a little bit more on this?

An example of using the calculate table function in target measures would be great, if you happen to see this and have one available.

Hi @Jaya,

 

Share a sample dataet, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.