CODEWITHSUNDEEP
powerbidax
ameyazing
ameyazing

Reputation: 423

Power BI: How to add slicer from a different table?

I'm trying to plot a cumulative line chart in PowerBI and add some slicers to it. Let me first describe my data.

My data looks as below: Original Data

To plot a cumulative chart, I'm creating a new table using below formula:

Cumulative = CALENDAR(DATE(2017, 9, 24), DATE(2017, 10, 12))

In this new table, I'm adding 2 more columns as below:

Issues_Logged = COUNTROWS(FILTER(Sheet1, Sheet1[Creation_Date].[Date] <= Cumulative[Date].[Date])) + 0
Issues_Resolved = COUNTROWS(FILTER(Sheet1, Sheet1[Resolution_Date].[Date] <= Cumulative[Date].[Date] && Sheet1[Resolution] = "Closed")) + 0

Now my completed table looks like this: Completed Table

Using above approach my cumulative chart plots fine and looks like this: Cumulative Chart

The problem I'm facing with this approach is that when I try to add a slicer for Group (to view this data group-wise), the slicer does not take effect. I know the reason is that 2 tables are not related. I can't see how to relate those 2 tables, or whether I need to change my approach altogether.

Can you help me resolve how to get the slicer working in this case?

Note: In future, original data may also include a field "Priority" and I may need to add a slicer for that too. So the approach needs to be scale-able to achieve that. Number of rows in original data is expected to cross 100K.

Upvotes: 0

Views: 1941

Answers (1)

Foxan Ng
Foxan Ng

Reputation: 7151

You should create the new fields as Measures instead of Calculated Columns if you want them to work correctly with other filters.

It is because Calculated Columns are calculated during data refresh according to the row it is in, while Measures are calculated at run-time and will take into account other contexts like filters applied and user selection.

See the results of Calculated Columns vs Measures when a filter is applied:

results

Upvotes: 1

Related Questions