cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dor Frequent Visitor
Frequent Visitor

Calculated Tables filtering

Hello,

We’re stuck with Power Bi and we’re ready to drop the idea of developing our reports with Power Bi. But I decided to describe our problems here, so maybe someone can help us. We started with 10 original custom reports developed with some old reporting technology. New reports should retain old filtering, which is StartDate, EndDate and few IDs. We also decided to pull all the relevant data into one huge view, so business users can pick whatever columns they want and filter with any of these columns (meaning that they can utilize all PowerBi possibilities). Some reports need some sort of calculated tables. That huge view has several measure columns. So, these calculated tables are created with UNION, e.g.:

TestTable = UNION(ROW("Category"; "Test1"; "Test11"; v_View[M_MeasureTest1]); ROW("Category"; "Test2"; "Test22"; v_View[M_MeasureTest2])) .

 

We have problems to filter such tables, meaning that if we create a visual with those 2 measure columns straight out of the huge view, we can filter them with other columns from the view (by creating a slicer tied to that column). But the visual created with our “Union” calculated table is not responding to changes in the slicer, i.e. we can’t filter that calculated table. It seems that these calculated tables are sort of a “snapshot” and that they can’t be filtered in real time.

 

If this is true, does anyone has a suggestion how to take some smaller subset of data from that huge view and create a visual from that smaller subset, that is filterable with slicer tied to any column from the original view?

 

Regards,

dor

3 REPLIES 3
Moderator v-caliao-msft
Moderator

Re: Calculated Tables filtering

Hi @dor,

 

I have tested it on my lecal environment, we can filter the data by using slicer.
TestTable = UNION(ROW("Category","Test1","Test11",'Table'[Measure]),ROW("Category","Test2","Test22",'Table'[Measure 2]))

 

Capture.PNGCapture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

dor Frequent Visitor
Frequent Visitor

Re: Calculated Tables filtering

Hello Charlie,

Thank you for the reply. We can also filter with columns from that exact calculated table (just like you did), but we want to filter with columns from the original view, from where the calculated table gets calculated. When we try to create a slicer that has a column from the original view , we can't filter the calculated table.

 

Regards,

dor

Moderator v-caliao-msft
Moderator

Re: Calculated Tables filtering

Hi dor,

 

We need to have a relationship between those two tables, so that we can filter data cross tables.

 

Regards,

Charlie Liao

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,606 guests
Please welcome our newest community members: