topic Finding and max value over two variables in table. in DAX Commands and Tips

Finding and max value over two variables in table.

xtwert55 — Thu, 21 Nov 2024 12:55:51 GMT

Hello, I have been stuck on this problem for a few days now.

I have a table of elements each with a bunch RAG status', date of RAG assessment and country of origin.

sheet1 (snippet of table)

Element	RAG	Date	Country
A	RED	01/02/24	USA
A	AMBER	01/03/24	USA
A	GREEN	01/04/24	Mexico
A	RED	01/05/24	Mexico
B	GREEN	01/06/24	USA
B	RED	01/07/24	Mexico
B	RED	01/08/24	USA

I want a DAX measure that counts the most recent RAG status for each element over each country. For instance for element A the most recent RAG for USA is AMBER (01/03/24) and the most recent for Mexico is RED (01/05/24). The reuslting table should look like below.

The resulting table should show:

Element	RAG	(DAX) Count of most recent RAG
A	AMBER	1
A	RED	1
B	RED	2

The reason I am contrained on what columns to use is because I want to put these as fields in a stacked bar chart.
x= element,
y = count ,
legend = status

I cannot create a summarized table as I need the table to be dynamic with a date slicer. I have written some DAX to achieve this but the reuslts are not as I expected.

I first created a virtual SUMMARIZE table to find the most recent RAG for each element and country. Then filtering the virtual table on sheet1 using TREATAS while also filtering on the current row conext using SELECTEDVALUE and then counting the result.

var _element = SELECTEDVALUE(Sheet1[Element]) var _rag = SELECTEDVALUE(Sheet1[RAG]) var _vtable = SUMMARIZE( sheet1, sheet1[Element], sheet1[Country], "RAG", CALCULATE( FIRSTNONBLANK(sheet1[RAG], 1), FILTER( sheet1, sheet1[Date] = MAX(sheet1[Date]) ) ) ) var _latest = FILTER(TREATAS ( _vtable, Sheet1[Element], Sheet1[Country], Sheet1[RAG]), Sheet1[Element] = _element && Sheet1[RAG] = _rag) return CALCULATE(COUNT(Sheet1[RAG]),_latest)

I added this dax to a table with columns sheet1[element], sheet1[rag], DAX_Measure. It appears to completly ingore the row context and produces nonsense.

Do you know what I am doing wrong? Any help would be appricated.

Re: Finding and max value over two variables in table.

johnt75 — Thu, 21 Nov 2024 13:42:50 GMT

You can use

Count of most recent RAG = VAR CurrentStatus = SELECTEDVALUE('Table'[RAG]) VAR BaseTable = CALCULATETABLE( 'Table', ALLEXCEPT( 'Table', 'Table'[Element] ) ) VAR SummaryTable = INDEX( 1, BaseTable, ORDERBY( 'Table'[Date], DESC ), PARTITIONBY('Table'[Country]), MATCHBY( 'Table'[Country], 'Table'[Date] ) ) VAR Result = COUNTROWS(FILTER( SummaryTable, 'Table'[RAG] = CurrentStatus )) RETURN Result

Re: Finding and max value over two variables in table.

xtwert55 — Thu, 21 Nov 2024 14:30:03 GMT

Thank you for response however I get the error: 'SummaryTable' is a table name and cannot be used to define a variable. When i write line 26: 'sheet1'[RAG] = CurrentStatus

Re: Finding and max value over two variables in table.

johnt75 — Thu, 21 Nov 2024 14:43:19 GMT

Do you already have a table called SummaryTable in your model? If so, just use _SummaryTable in the measure instead.

Re: Finding and max value over two variables in table.

FreemanZ — Thu, 21 Nov 2024 14:51:33 GMT

hi xtwert55 ,

try like:

Measure =

VAR _table =

ADDCOLUMNS(

SUMMARIZE(

data, data[Element], data[Country]

"LastRag",

VAR _date = CALCULATE(MAX(data[date]))

RETURN CALCULATE(MAXX(FILTER(data, data[date]=_date), data[RAG]))

)

VAR _result =

COUNTROWS(

SUMMARIZE(_table, [LastRag])

)

RETURN _result

Re: Finding and max value over two variables in table.

xtwert55 — Thu, 21 Nov 2024 14:53:49 GMT

You are a hero! Yes this seems to be working perfectly now. Thank you very much 🙂