Calculated Column is not working with report filters

[Anonymous]

Hi Everyone,

     I have a pie chart that displays the hours spent on different projects. Because there are a lot of projects, I created a new column that displays the names of the first 10 projects that has the top hours and then group the remaining as 'Other Projects".

      I have this DAX that gave me the desired result, however, I noticed when I apply a filter from another chart, this doesnt seems to work well.

For instance, if I select a filter that will return like 4 pies and these pies should show the names of all the 4 projects since they are just four of them. However, this will show the 3 projects and the fourth one will be showing "Other Projects".

Project Group = 
IF (
	CONTAINS ( 
		TOPN (
			10,
			SUMMARIZE ( 
				'Projects',
				[Project Name],
				"Total Hours",
				SUM ( 
				'Projects'[Hours]
				)
			),
			[Total Hours]
		),
		'Projects'[Project Name],
		'Projects'[Project Name]
	) = TRUE (),
	[Project Name],
	"Other Projects"
)

Please I need someone to help me review the DAX to see what I am missing.

Thanks!

[amitchandak]

@Anonymous , refer if these can help

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

https://community.powerbi.com/t5/Desktop/Top-10-Other/td-p/51282

https://www.youtube.com/watch?v=UAnylK9bm1I

[Greg_Deckler]

@Anonymous - Calculated columns are not dynamic based upon report filters, etc. They are not recalculated based upon user interaction. I think this is your issue if I am following this correctly.

[Anonymous]

Yes, they are not dynamic

[Anonymous]

Hi @Anonymous,

In fact, filter/slicer and calculate columns are at different data levels. The source table that filter/slicer is generated from data model tables that calculated fields hosted. 

Notice: data level from parent to child, the child not able to affect their parent level.

query table(query, query parameter, custom function...) -> data model table(calculate table, calculate column, RLS) -> date view table (measure, slicer, filter)

So if you want to create a dynamic calculated column/table, you need to use the high-level filter than data model tables.
For example, you can create a query parameter and turn on the 'enable load' option to save it as a query table. Then your table can interact and calculate with the generated query parameters table values. (you can change the query parameter to change the query parameter table to indirect change the calculated table results)

Regards,

Xiaoxin Sheng