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 value...
  • 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])
jolind1996's avatar
jolind1996
Icon for Resolver II rankResolver 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