Dynamic Filtering of a table based on measures

[Anonymous]

Hello, 

I am trying to create a line chart that compares two lines. One line is a simple sum, the other line is a reference that is calculated using a measure.

In order to achieve this, i use two tables: 

• Table 1 contains the "Master Data" and all filter options   
• Table 2 contains percentages for each Month that are used to calculate the reference line

Table 1 is affected by all slicers in the report.

Table 1 also contains a measure (which is also a sum with a few conditions) that is later used in table 2.

Apart from the months, there are no relationships connecting table 1 and table 2. 

Filtering the measure and the "sum line" using a slicer in the report works as intended.

The Measure from Table1 is used to create a calculated column in table 2 by multiplying the percentages in table 2 with the value of the Measure. 

This works well if I do not apply any filters to the report. However, when I do, the calculations in Table 2 (and therefore the line in the visual) are not affected.

Is there a way for me to dynamically filter Table 2 based on the slicer filtering table 1 and thus the Measure that is used for the calculated column in Table 2? 

If that is not possible, is there a way to create a Measure or something similar that can be used in a line chart? 

Thank you in advance! 

Jonas 

[dk_dk]

Calculated columns by design will not be affected by slicer changes. Calculated columns are calculated once when the data is loaded (and subsequently when the data is refreshed) but not when it is filtered by slicers, selections, or anything of the like. You should try to reproduce the same result using measures only.

[Anonymous]

Thank you for your answer! That's what i thought.

I have now tried to simply calculate the percentage for each row and let the visual sum it up, which works fine. 

However, i now have the issue that i need three columns which use similar semantics. 

I multiply the value of a row in t able1 with the corresponding reference in table 2. This works well for the first column, but when i try to do the same with the next column I get an error due to a cycle Dependency. 

I map the two columns based on a month column. In an effort to resolve the issue I have tried: 

• using RELATED
  • with an established relationsthip between table 1 and table 2 
• using LOOKUPVALUE
  • with and without a relationship
• using CALCULATE
  • with and without a relationship

At this point, I really do not know what else to try. I also do not know why the cycle dependency would come up in the first place.