Forum Discussion

Farwest's avatar
Farwest
Frequent Visitor
6 years ago

Dynamic max calcuation in Power Query

Hi,

 

I am trying to calcualte a dynamic Max value from one column in Power Query Editor.

An example below where I have different groups, I would like to be able to have dynamic Max value measurement based on one selected group, but not only I would to be able to measure the Max among several number of groups and all the groups.

I am using the Max value for the following step in Power Query to define boundaries.

 

 

The final goal is to be able in Power Bi viewer to dynamically change these calculated boundaries based on the groups selected for a same visual.

 

Thanks for the help

Cheers

Need Help

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Farwest ,

     

    As artemus  said, we cannot achieve that by power query. We can create a meaure by DAX as below to work on it.

    MAX = CALCULATE(MAX('Table'[Probability]),ALLSELECTED('Table'))

     

    For more details, please check the pbix as attached.

     

    • Farwest's avatar
      Farwest
      Frequent Visitor
      6 years ago

      Thank you for the answer,

       

      I managed to get out of this problem.

      I am still struggling with some other dynamic calculations.

       

      I am trying to use a filter in order to set a measure, it is a single selection filter.

      Selected = if(HASONEVALUE('Table['Name]);SELECTEDVALUE('Table[Name]);"ALL")

      I am getting the selected filter through this and using switch to select a column based on the selection
       
      SWITCH([Selected];
      "ALL";MAX(Table[Sales 1]);
      MAX(Table[Sales 2]))
       
      here I would like to use a column if nothing is selected (Table[Sales 1]) and the other column (Table[Sales 2]) for any single selection filter. it does not look to be working as "ALL" is not in Table[Name]. is there a workaround for this.
       
      Please let me know if I must create a new topic for this
       
      Thanks,
      • Farwest's avatar
        Farwest
        Frequent Visitor
        6 years ago

        Dear Community,

         

        I am still struggling with the filter selection, 

        I created a measure to get the selected value or get "ALL" instead

         

        Selected = if(HASONEVALUE('Table'[Tool Name]);SELECTEDVALUE('Table'[Tool Name]);"ALL")
         
        Measure = SWITCH([Selected];
        "ALL";MAXX('Table'[Result 1]);
         MAX(''Table'[Result 2]))
         
        Does not seem to swith, I always have the same column returned.
         
        Thanks.
  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    I'm not entirly sure what you are trying to accomplish, but...

     

    1. If you have loaded all the data you need to calculate the values, you should be able to write a dax measure which does what you want. Chances are one of the quick measures that is offered will work.

     

    2. If you are using direct query, and have not loaded the data needed to calculate the value, then you will probably need to wait until the filter on parameter is implemented in Power Bi. Doing this your self is possible, but quite difficult, and has many caveats.