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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
quantfinRguy
Frequent Visitor

Why is my calculated column ignoring slicers

To preface, I cannot do a bi-directional relationship between these two data sets due to model complexity so do not suggest that.

 

I have a date table and a data set.

 

The data set is composed of columns [Client], [Date], [Measure]. I have a slicer on [Client].

 

Not all clients have measures on all dates, i.e. one may have measures for July 16 to July 17, while another may have measures from June 14 to July 17. I want to be able to filter a date slicer so that the client that only has measures for July 16 and onwards will only show a timeline for July 16 to July 17 on the slicer, rather than show June 14 to July 17 for any selected client despite a lack of data.

 

To do so, I created a calculated column in my date table. The calculation is =IF(`date table`[date] IN VALUES(`data set`[date]), [date], BLANK())

 

I expected my [Client] slicer to filter down the `data set` table, and then the calculated column would display BLANK() for all the dates in the date table not observed for the chosen [Client]. That did not happen. The calculated column is ignoring the [Client] slicer entirely.

 

How do I fix this? 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

calculated columns are treated as „normal“ columns by the data model, with the sole difference in clmparison to the othet columns, they do not exist in the data source. For this reason just the normal aggregation functions will show a differing values if the rows of the conaining table are filtered down by the use of slicers.

For this reason you have to define a measure instead of a calculated column.

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

Hey,

calculated columns are treated as „normal“ columns by the data model, with the sole difference in clmparison to the othet columns, they do not exist in the data source. For this reason just the normal aggregation functions will show a differing values if the rows of the conaining table are filtered down by the use of slicers.

For this reason you have to define a measure instead of a calculated column.

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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