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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
david_blabla
Frequent Visitor

Filter does not work on calculated column

Hi all,

 

I ran into a problem recently and hope that someone knows what I am doing wrong:

I have two data sets which are mapped by an ID.

The element dataset looks like this:

elementsummary_IDfinish dateelement type
a123.09.21Type 1
b122.09.21Type 2
c221.09.21...

Summary dataset looks like this:

IDSummary Finish Date (see below)
1...
2...

I am calculating the Summary Finish Date as the last finish date of all included elements using this formular:

 

 

Summary Finish Date = CALCULATE(
        MAX(element[finish date]),
        FILTER(element, element[summary_ID] = summary[ID]))

 

 

Afterwards I pulled in "ID" and "Summary Finish Date" both from the summary dataset into a table on a PowerBI dashboard. On the same dashboard I also included a filter by "element type" from the element dataset.

Unfortunately, that filter does not do anything for me. When I filter for Type 2 the displayed date will still be 23.09.21 for ID 1.

Only if I include the filter into the calculated column directly it works:

 

 

Summary Finish Date = CALCULATE(
        MAX(element[finish date]),
        FILTER(element, element[summary_ID] = summary[ID] && element[element type] = "Type 2"))

 

 

The result now is 22.09.21 - As expected.

Any idea what I have to do to get the variable filter to work?

 

Thanks,

David

6 REPLIES 6
smpa01
Super User
Super User

@david_blabla  if I understand it correctly,

 

you created 'Summary Finish Date' calculated column in 'summary'

and

in your data model 'summary' filters 'element' in 1 to Many.

 

In the viz, you brought all your AXIS (columns) from 'summary' and expects that Viz to be sliced by an element (element type) from 'element'.

 

Since 'element' does not filter 'summary' that slicer would not work.

 

In order to make it work,

you can build an 'element type' table and connect it to 'element' 

and

write a measure 

and

bring your AXIS from both 'summary' and 'element type' table

and 

bring slicers from all the dimension tables ('summary','elemnt type')

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi @smpa01,

 


@smpa01 wrote:

Since 'element' does not filter 'summary' that slicer would not work.

That sounds very promising.

Unfortunately, I did not understand your solution. Can you maybe elaborate a bit more?

 

Thanks,

David

@david_blabla  I am guessing your DM looks like this

 

smpa01_0-1632406061279.png

 

The flow of filter (arrow) is from summary to element, which means only columns from summary can slice anything from element, summary can slice summary. But element can't slice sumamry unless you make the filter bi-directional (which is not recommended) but then it introduces a lot of ambiguity in the model.

 

What I have suggested is this. When building viz, bring all the axis and slicers from from 'elementType' and 'summary'; create a measure on 'element' and drop that on the axis.  Slicer can easily slice them.

 

smpa01_1-1632406261454.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
Super User

@david_blabla , we can not use slicer values in a calculated column. We need to work with measure only

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak, I can not believe that is true as on other pages I have pulled calculated columns into tables as well and the displayed data get's filtered based on the slicers.

@david_blabla , a measure can use slicer value. Not the column, that is what I know.  Can you share a sample pbix after removing sensitive data.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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