Forum Discussion

Scott-RSM's avatar
Scott-RSM
New Member
2 years ago
Solved

Dynamic Calculated Table and Get Min & Max Values

Hi There, 

 

I have been struggling to get the max and mins from a calculated table through a measure.

 

I am looking to group an imported table by a unique key and then get the max and min values. and I am getting this through the following:

 

var _group = GROUPBY(ALLSELECTED('WIP Entries'),'WIP Entries'[zLinkKey],"Billing Group Natural Profit",SUMX(CURRENTGROUP(),'WIP Entries'[Natural Profit]))
var _minX = MINX(_group,[Billing Group Natural Profit])
var _maxX = MAXX(_group,[Billing Group Natural Profit])
 
Where I am getting stuck is when I apply slicers which filter the 'WIP Entries' table I want my '_group' calculated table to update to only use the data which it has been filtered to. So my min and max results should change, but they currently still remain to an unfiltered grouping.
 
I hope this makes sense, please reach out if you have any questions.
 
Thanks
  • Scott-RSM's avatar
    Scott-RSM
    2 years ago

    I was able to work out my problem - was more to do with where I was creating the new table / column and how my visuals would be filtering. Below is the DAX which resolved it:

     

    var _group = ADDCOLUMNS(ALLSELECTED('Billing Group Details'), "Billing Group Natural Profit", CALCULATE(SUM('WIP Entries'[Natural Profit]), FILTER(ALLSELECTED('WIP Entries'),'WIP Entries'[zLinkKey] = FIRSTNONBLANK('Billing Group Details'[zLinkKey],TRUE()))))
    var _minX = MINX(_group,[Billing Group Natural Profit])
    var _maxX = MAXX(_group,[Billing Group Natural Profit])

4 Replies

  • jolind1996's avatar
    jolind1996
    Resolver II
    2 years ago

    It seems that you are trying to create a dynamic calculated table that updates based on the filters applied to the 'WIP Entries' table. One way to achieve this is to use the ALLSELECTED function in your _group variable to consider the filters applied to the 'WIP Entries' table. Here is an example of how you can modify your _group variable to achieve this:

    var _group = GROUPBY(
    ALLSELECTED('WIP Entries'),
    'WIP Entries'[zLinkKey],
    "Billing Group Natural Profit",
    SUMX(
        CURRENTGROUP(),
        'WIP Entries'[Natural Profit]
    )
)

    In this example, the ALLSELECTED function is used to consider the filters applied to the 'WIP Entries' table when grouping the data. This should update the calculated table based on the filters applied to the 'WIP Entries' table, and the _minX and _maxX variables should also update accordingly.

    Best regards,

    Johannes

    • Scott-RSM's avatar
      Scott-RSM
      New Member
      2 years ago

      Hi Johannes,

       

      Thanks for the response but the ALLSELECTED() function was already in my DAX and still not filtering per the slicers.

       

      I tried using the SUMMARIZE function as an alternative to the GROUP function and while that seemed to dynamically update I wasn't able to get the ultimate min and max of the dataset, it would just return the min and max of each row.

       

      Any other thoughts?

       

      Thanks

      Scott 

  • Scott-RSM's avatar
    Scott-RSM
    New Member
    2 years ago

    I have done some further testing and found out that when I filter directly to the 'WIP Entries' table the above dax works as expected. The problem seems to be when I am filtering another table which has a relationship to the 'WIP Entries' Table it doesn't apply the filter.

     

    Any thoughts on how to work around this?

     

    Thanks

    Scott

  • Scott-RSM's avatar
    Scott-RSM
    New Member
    2 years ago

    I was able to work out my problem - was more to do with where I was creating the new table / column and how my visuals would be filtering. Below is the DAX which resolved it:

     

    var _group = ADDCOLUMNS(ALLSELECTED('Billing Group Details'), "Billing Group Natural Profit", CALCULATE(SUM('WIP Entries'[Natural Profit]), FILTER(ALLSELECTED('WIP Entries'),'WIP Entries'[zLinkKey] = FIRSTNONBLANK('Billing Group Details'[zLinkKey],TRUE()))))
    var _minX = MINX(_group,[Billing Group Natural Profit])
    var _maxX = MAXX(_group,[Billing Group Natural Profit])