Min, Max & Avg slicer based on other filters

DAX DAX Calculations
2

Hi @khurram ,

Thank you for waiting.

In this solution I add third Slicer (Selection 3) - Min, Max, Avg and then make measure calculation based on Selection 1, 2 and 3.

I used measure branching technique - build measure on top of other.

Short description:

So we have 3 selections :

  • measure selection (Selection 1 slicer)

  • granularity selection (Selection 2 slicer)

  • Min, Max, Average function selection (Selection3)

  1. Slicer 1 (Measure selection ) - additional measure is created:
Measure Selection1 = 
 Var _MetricSelection =  selectedvalue('Selection 1'[Selection 1 Order])

    Return
        switch(_MetricSelection,

        0, [Price],
        1, [Sales]
            )
  1. and then I create sub-mesure for each function that you would like to have on Slicer 3 - minimum example (but it similar for max and average) - both Selection 1 (Measure) and Selection 2 (granularity) is used:

  • select granularity - and check scenarios based on the selection

  • for measure - previous created [Measure Selection1] is used

min value = 

    var _granularity = SELECTEDVALUE('Selection 2'[Selection 2 Order]) 

    Return
    if(isblank([Measure Selection1]),blank(),
                calculate( 
                    switch(_granularity,
                        0,minx(filter(values('Calendar'[Month & Year]),[Measure Selection1]>0),[Measure Selection1]),
                        1,minx(filter(values('Calendar'[Quarter & Year]),[Measure Selection1]>0),[Measure Selection1]),
                        2,minx(filter(values('Calendar'[Months]),[Measure Selection1]>0),[Measure Selection1])
                        ),
                    
                     ALLSELECTED('Calendar'
                    ))
   )
  1. and then you have all bring together ( Selection 1 (measure), Selection 2 (granularity) and Selection 3 ( function)
Measure Selection3 = 
 Var _MetricSelection =  selectedvalue('Selection 3'[Selection 3 Order])

    Return
        switch(_MetricSelection,

        0, [min value],
        1, [max value],
        2,[average value]
            )

So you can use and display i.e on chart like this

image
image1908×850 51.2 KB

This is a basic concept. Later it can be formatted, Hide title like :

etc.

Sample pbix file:
Sample Data for Dynamic Slicers_khurram.pbix (109.9 KB)

Hope this is what you’re looking for

1 Like