Finding and max value over two variables in table.

[xtwert55]

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. 

[johnt75 · Accepted solution]

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

[FreemanZ]

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

[johnt75 · Accepted solution]

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

[xtwert55]

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

[johnt75 · Accepted solution]

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

[xtwert55]

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