Use Measure as slicer is not working properly

[coding7]

Hi, 

I have a matrix with 3 levels:

1. 1st level Reseller;
2. 2nd level Contract Name;
3. 3rd level Year Hierarchy: Year, Quarter, and Month.

And I have the measure Revenue = SUM ( 'Fact Invoice'[Invoice Amount] )

I created the Revenue measure as a filter source video:

I created a calculated table

Revenue Filter = GENERATESERIES(0, [Revenue], 100)

and then created this measure:

Revenue Filter Measure = VAR MinValue = MIN ( 'Revenue Filter'[Value] ) VAR MaxValue = MAX ( 'Revenue Filter'[Value] ) VAR currentMeasure = [Revenue] RETURN IF ( currentMeasure >= MinValue && currentMeasure <= MaxValue, 1, 0 )

In the visual filter, I set this measure = 1 so that the slicer could filter the matrix, and it is filtering.

The problem is that it doesn't show the data correctly.

I tried to create this measure:

Revenue Filter AGG measure = 
VAR MinValue =
    MIN ( 'Revenue Filter'[Value] )
VAR MaxValue =
    MAX ( 'Revenue Filter'[Value] )
VAR currentMeasure = [Revenue]
--VAR CurrentContract = MAX('Dim Contract'[Contract Intermediary Account Name 1])
 
RETURN
IF (
        ISINSCOPE('Dim Contract'[Contract Intermediary Account Name 1]),
        CALCULATE(
            IF (
                currentMeasure >= MinValue && currentMeasure <= MaxValue,
                1,
                0
            ),
            ALL('Date')
        )
        ,0
    )

to then filter it in the filter pane = 1, to give me the correct filtered values, but without success.

As shown in the image below, for Reseller 3, it is correct because the total is between the values I set in the slicer (between 2k - 5k).

But for Resellers 1 and 2, it is not correct because the total is greater than the values I am filtering in the slicer. However, since the other levels have values that match the revenue filter, they are appearing.

That is, for Reseller 1, everything should be 0 because the total does not correspond to the revenue slicer filtering.

The same issue may occur for Reseller 3, for example, having a month with 300€, January 2891.36€, and February 300€; this 300€ value should not appear either.

Sample data:

Reseller,Contract Hierarchy,Year,Quarter,Month,Revenue
Reseller 2,Contract_1,2023,Q1,March,€ 3 692,09
Reseller 2,Contract_1,2023,Q1,February,€ 3 508,58
Reseller 3,Contract_3,2023,Q1,January,€ 2 891,36
Reseller 1,Contract_2,2023,Q2,May,€ 2 869,66
Reseller 2,Contract_2,2023,Q4,November,€ 2 187,18
Reseller 3,Contract_2,2023,Q4,October,€ 2 146,18

Can anyone help me to achieve this ?

Thanks a lot!

[Anonymous · Accepted solution]

Hi @coding7 

Maybe you can try this:

First of all, I did some processing with your data:

Then add a measure:

Measure = 
SUM('Table'[Column])

Next add the second measure:

Measure 2 = 
	VAR _minValue = 250000
	VAR _maxValue = 600000
	VAR _currentRe = SELECTEDVALUE('Table'[Reseller])
	VAR _sumRe = CALCULATE(
		SUM('Table'[Column]),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Reseller] = _currentRe
		)
	)
	RETURN
		IF(
			_sumRe <= _maxValue && _sumRe >= _minValue,
			IF(
				[Measure] <= _maxValue && [Measure] >= _minValue,
				1,
				0
			),
			0
		)

The result is as follow:

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.