topic Re: How to use 2 filtered measures as input for another measure and put them in the same page in DAX Commands and Tips

How to use 2 filtered measures as input for another measure and put them in the same page

witheringarc5 — Tue, 17 Dec 2024 09:44:08 GMT

I have 2 slicers, slicer A and B; 3 measures, measure A, B and C

column A and B are in the same table

slicer A is using column A
slicer B is using column B, duplicated from column A

slicer A filters measure A

slicer B filters measure B

measure C uses measure A and B as input after filtering measure A and B with slicer A and B, with this calculation:

measure C = (measure A - measure B)/measure B

I need to put all of them in the same page, how to show the result of measure C without being affected by slicer A and B? turning off interaction between measure C and the slicers will return blank values, while turning on will return 0

everything is correct except measure C showing either blank or 0

please provide help, thanks

Re: How to use 2 filtered measures as input for another measure and put them in the same page

123abc — Tue, 17 Dec 2024 09:50:59 GMT

Use the ALL function to remove the slicers' influence on Measure C. The ALL function clears filters on a specified table or column, allowing you to calculate Measure C without being affected by the slicers.
Modify Measure C to use the unfiltered versions of Measure A and Measure B.

Here's how to modify your measures:

Measure A:

Let's assume your Measure A is something like:

Measure A = SUM('Table'[ColumnA])

This measure is filtered by Slicer A.

Measure B:

Similarly, Measure B would look like:

Measure B = SUM('Table'[ColumnB])

This measure is filtered by Slicer B.

Measure C:

Now, you need to modify Measure C to use Measure A and Measure B without the slicers affecting it.

Measure C = VAR FilteredMeasureA = CALCULATE([Measure A], ALL('Table'[ColumnA])) VAR FilteredMeasureB = CALCULATE([Measure B], ALL('Table'[ColumnB])) RETURN IF(FilteredMeasureB <> 0, (FilteredMeasureA - FilteredMeasureB) / FilteredMeasureB, 0)

Result:

This approach ensures that Measure C is calculated based on Measure A and Measure B, but the slicers will not affect Measure C. It should display the correct result instead of returning blank or zero.

Putting Measures on the Same Page:

You can add Measure A, Measure B, and Measure C to the same report page. As Measure C is calculated without considering the slicers, it will reflect the desired output regardless of the slicer selections.

Re: How to use 2 filtered measures as input for another measure and put them in the same page

witheringarc5 — Tue, 17 Dec 2024 10:12:39 GMT

I'm using the exact method you just shared, it doesn't work, still show as 0

turning on/off interaction has no effect

Re: How to use 2 filtered measures as input for another measure and put them in the same page

123abc — Tue, 17 Dec 2024 12:57:15 GMT

If using REMOVEFILTERS or ALLSELECTED doesn't resolve the issue and Measure C still returns 0, it's likely that Measure A or Measure B evaluates to 0 or blank under the slicer context. This could happen if filters are being applied in an unintended way.

Here's an alternative method to ensure that Measure C works correctly without being affected by slicers:

Alternative Solution: Using Variables with Slicer Context Explicitly Preserved

Instead of relying on REMOVEFILTERS, explicitly capture the filtered values of Measure A and Measure B in variables.

Modify Measure C as follows:

Measure C = VAR FilteredMeasureA = CALCULATE( [Measure A], ALL('Table'[Column B]) ) VAR FilteredMeasureB = CALCULATE( [Measure B], ALL('Table'[Column A]) ) RETURN IF( FilteredMeasureB <> 0, (FilteredMeasureA - FilteredMeasureB) / FilteredMeasureB, BLANK() )

Key Changes:

ALL instead of REMOVEFILTERS or ALLSELECTED:
- ALL('Table'[Column A]) clears only the filter from Column A (slicer A), and ALL('Table'[Column B]) clears Column B (slicer B). This isolates the behavior.
Using Variables:
- We store the filtered measure values into FilteredMeasureA and FilteredMeasureB. This ensures we are capturing values explicitly while bypassing cross-filter issues.
Testing for Division:
- A safeguard ensures that Measure B is non-zero before division.

Why It Should Work:

Measure A retains its slicer A context, and column B slicer is ignored.
Measure B retains its slicer B context, and column A slicer is ignored.
By removing filters only from one specific column, the slicer filtering each measure works independently without overlapping or zeroing out the values.

Debugging Steps:

Add Measure A and Measure B to a table visual alongside their slicers to confirm they return the correct values.
Use a card visual to display the output of Measure C and verify it is not impacted by slicer interaction.

If you still encounter issues, it might be due to data model relationships or filter propagation. Let me know, and I can guide you further!

Re: How to use 2 filtered measures as input for another measure and put them in the same page

Anonymous — Wed, 18 Dec 2024 06:42:49 GMT

Hi witheringarc5 ,

When does there be a blank or 0? Does it happen when you select different values in the two slicers?

I recommend that you create calculated tables. This is because you are using two columns with the same value as Slicers, and once the values selected in the two Slicers are different, the table returns a blank row(Slicer will filter table according to selected value). This can be solved by using calculated tables, isolating the filters for A and B.

Here is my test for your reference.

TableA = VALUES('Table'[ColumnA])

TableB = VALUES('Table'[ColumnB])

Measure A = IF(ISFILTERED(TableA[ColumnA]),CALCULATE(SUM('Table'[ColumnA]),'Table','Table'[ColumnA] in ALLSELECTED(TableA[ColumnA])),0)

Measure B = IF(ISFILTERED(TableB[ColumnB]),CALCULATE(SUM('Table'[ColumnB]),'Table','Table'[ColumnB] in ALLSELECTED(TableB[ColumnB])),0)

Measure C = ([Measure A]-[Measure B])/[Measure B]

When I selected values in two Slicers, the result showed as following.

Best regards,

Mengmeng Li

Re: How to use 2 filtered measures as input for another measure and put them in the same page

witheringarc5 — Wed, 18 Dec 2024 08:55:12 GMT

I tried to use your method, measure C is still returning 0 even after I removed the if statement. It seems like when I put all the visuals together, both slicers are filtering it.

When nothing on the page is interacting with measure C, it returns 0 because it uses all the values of column A and B for calculation, which returns the same number for measure A and B, i.e. [measure A]-[measure B]=0, zero divided by any number is still zero.

However, there are two interesting phenomena when I only activate interaction between measure C and slicer A/B while both measure A and B are filtered and returns correct values.

When I only activate the interaction between measure C and slicer A, it returns 0, which should not be like this because the expected outcome is that measure A will return a number smaller than that of measure B, measure C should return a negative value. On the other hand, only activating interaction between measure C and slicer B should return a positive value but zero is returned instead.

My hypothesis is that, since measure A and B are using the same column for calculation but they are using different slicers with different columns, so there are 2 sets of different values from different columns filtering the same column and causes the DAX to not work.

My ideals: it would be awesome if measure C can use the filtered results of measure A and B without being affected by slicers and the problems mentioned.

I'm not sure if my DAX knowledge need to drill down to the equivalent level of understanding how java compiler compiles java code to machine code. If yes, that would be very time-consuming but I would do it at my free time, and I hope you guys can guide me where to start or provide useful resources.

Or maybe this is a limitation of power bi.

Re: How to use 2 filtered measures as input for another measure and put them in the same page

Anonymous — Mon, 23 Dec 2024 07:13:31 GMT

Hi witheringarc5 ,

Yes, it's really because you're using two columns in the same table as filters. Turning off the interaction doesn't turn off the filter's filtering of the table, you should create calculated tables that copies the values of these two columns, and then uses the columns in the calculated table as filters. Please refer to my previous reply for detailed steps. If you have any questions, please feel free to contact me.

Best regards,

Mengmeng Li

Re: How to use 2 filtered measures as input for another measure and put them in the same page

witheringarc5 — Mon, 23 Dec 2024 07:23:35 GMT

my company's power bi service is on halt due to capacity issues, the semantic model cannot be loaded, with xmas holidays coming up, probably cannot test until next year😂

Merry Xmas🎊