Dynamic Calculated Table and Get Min & Max Values

[Scott-RSM]

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 · Accepted solution]

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])

[Scott-RSM]

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

[jolind1996]

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]

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