Forum Discussion

corporate's avatar
corporate
Icon for Helper I rankHelper I
6 years ago
Solved

Filter from measure in a table

Hi everyone,

I am new on Power BI and I'm trying to build a filter from a measure in a table. Here is my problem:

 

1) I import a table in Power BI with raw data: production values grouped by several columns (Age, Province...) 

 

2) I create a table in Power BI adding up Production values grouped by Province, and this table reacts to page filters (Age, Region, Province...) selected by the users

 

3) I want to create a slice filter for Production values in the Power BI table: in this case, my filter should move between 3.620 and 8.100, and if I choose the highest value in the filter, Power BI table should show me only the first row.

When I change the other filters in the page, I want both my filter and my table to fit.

 

I have no idea if this is possible. Do you have any suggestion?

 

Thank you very much!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, corporate 

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    Test (a calculated table):

     

     

    Test = GENERATESERIES(0,10000,1)

     

     

     

    You may create three measures as below.

     

     

    Slicer Control = 
var t =
SUMMARIZE(
    'Table',
    'Table'[Region],
    'Table'[Province],
    "MeasureValue",CALCULATE([Measure])
)
return IF(
    COUNTROWS(
        FILTER(
            t,
            [MeasureValue]=SELECTEDVALUE(Test[Value])
        )
    )>0,
    1,
    -1
)

Table Control = 
IF([Measure] in DISTINCT('Test'[Value]),1,-1)

Measure = SUM('Table'[Production])

     

     

     

    Then you need to put 'Slicer Control' in the visual level filter of 'Value' slicer and put 'Table Control' in the visual level filter of table visual.

     

     

    Best Regards

    Allan

     

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

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, corporate 

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    Test (a calculated table):

     

     

    Test = GENERATESERIES(0,10000,1)

     

     

     

    You may create three measures as below.

     

     

    Slicer Control = 
var t =
SUMMARIZE(
    'Table',
    'Table'[Region],
    'Table'[Province],
    "MeasureValue",CALCULATE([Measure])
)
return IF(
    COUNTROWS(
        FILTER(
            t,
            [MeasureValue]=SELECTEDVALUE(Test[Value])
        )
    )>0,
    1,
    -1
)

Table Control = 
IF([Measure] in DISTINCT('Test'[Value]),1,-1)

Measure = SUM('Table'[Production])

     

     

     

    Then you need to put 'Slicer Control' in the visual level filter of 'Value' slicer and put 'Table Control' in the visual level filter of table visual.

     

     

    Best Regards

    Allan

     

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    corporate yes it can be achieved using visual level filter, create a what-if parameter with your range and then based on value selected in the what-if parameter, create a measure like below and use this following measure in visual filter by selecting where value is 1

    Filter Measure = 
IF ( [Production Measure] >= [What if parmeter value], 1, 0 )

     

    I would ? Kudos 🙂 if my solution helped. ?

     

      • corporate's avatar
        corporate
        Icon for Helper I rankHelper I
        6 years ago

        Thank you parry2k , but unfortunately this is not  what I need, or I didn't understand how to use it.

        From what I see, the what-if parameter must be created manually; I want a filter that automatically adjusts it's maximum and minimum values every time I change the filters on the page and the Production values in the Power BI table change.

         

        Let me know if I haven't well explained my problem.

         

        Thank you!