Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Column is not working with report filters

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!

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, they are not dynamic

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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