Forum Discussion

PBI-Guy's avatar
PBI-Guy
Helper I
4 years ago
Solved

Summarize table with dynamic filtering

Hello Team, I need some major help to solve this issue. I have a summarized calculated table that I need to be able to filter dynamically. Below is a sample of the calculated table that I have a union joining on. The first table in the union is "Brand EPA" and is an average from the full Brand EPA table. 

 

 

Below is the full Brand EPA table where the summarized calculated table is pulling from however I need a way to have the results be dynamic when I filter on Sex, Age, Education, Income and BrandOwn. This will change the average from being static to dynamic in the summarized table.

 

Thanks all,

 

Sean

crossfilter
DAX
Filter
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    OK. We can certainly compute distances without needing an extra calculated table in the model. For example,

     

    Closest Profile =
VAR E1 = AVERAGE ( Survey[Element1] )
VAR E2 = AVERAGE ( Survey[Element2] )
VAR E3 = AVERAGE ( Survey[Element3] )
RETURN
    MINX (
        TOPN (
            1,
            Profile,
            ( E1 - Profile[_Element1] ) ^ 2 +
			( E1 - Profile[_Element1] ) ^ 2 +
			( E1 - Profile[_Element1] ) ^ 2,
			ASC
        ),
        Profile[Profile]
    )

     

    You can make all this dynamic without a calculated table:

    See attached.

  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Kudos to AlexisOlson for asking the right question to move this forward!

    Rather than TopN do what you want:

    Avg Distance Rank = 
    RANKX(
        ALLSELECTED(Profile[Profile]),
        [Avg Distance],
        ,
        ASC,
        Dense
    )

    and use it in the visual filter?

     

21 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    A calculated table (and calculated columns) are always formed when the model is refreshed. So by definition are static.

     

    Your options are:

     

    1) Change the granualarity of your summarised table by adding in Sex, Age, Education, Income and BrandOwn. This means that the average is pre calculated for every combination.

     

    2) Switch away from a calculated table and make use of measures which are dynamic.

    • PBI-Guy's avatar
      PBI-Guy
      Helper I
      4 years ago

      bcdobbs I originally thought that the use of measures would be better however I don't have the know-how to achieve that hence why I took the calculated table path. Do you have any recommendations? 

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Ok, so if you just need a straight average that responds to filters in visuals you can go really simple.

         

        1) Right click your table and click create measure.

         

        2) Add measures for each summary you need.

         

        eg

         

        Avg Potency = AVERAGE(TableName[Potency])

         

        Then add a table visual and add your normal columns and the measures at the end.

         

        If you then have slicers for the other things everything will update.

         

        If your requirements are more complex are you able to send a demo file and a requested output. Will happily write some measures for you!