Forum Discussion
DAX Help - Measure to find difference
Community, I am having difficulty writing DAX to find the differnce between various test results. Seamed simple. Any help on the topic is apperciated. Below is example data and what I would lik...
After looking at Anonymous , using the LOOKUPVALUE() function makes more sense
Dif2 = VAR vCurrentTestIDtoRef = 'Table1 (2)'[Test ID to Ref] VAR vCurrentTestMap = 'Table1 (2)'[Test Map] VAR vRefNum = LOOKUPVALUE ( 'Table1 (2)'[Data_Col], 'Table1 (2)'[Test ID], vCurrentTestIDtoRef, 'Table1 (2)'[Test Map], vCurrentTestMap ) RETURN 'Table1 (2)'[Data_Col] - vRefNum
Anonymous
Alright, then we could use a FILTER() function to capture all records matching the condition, after that we use a SELECTCOLUMNS() function to keep only the column that contains the data that we want. And then last, we use FIRSTNONBLANK() to keep and return the first value in the list.
Does that work?
Dif2 =
VAR vCurrentTestIDtoRef = 'Table1 (2)'[Test ID to Ref]
VAR vCurrentTestMap = 'Table1 (2)'[Test Map]
VAR vFilteredTable =
FILTER (
'Table1 (2)',
AND (
'Table1 (2)'[Test ID] = vCurrentTestIDtoRef,
'Table1 (2)'[Test Map] = vCurrentTestMap
)
)
VAR vKeepColData =
SELECTCOLUMNS ( vFilteredTable, "NewData_Col", 'Table1 (2)'[Data_Col] )
VAR vFirstValue =
FIRSTNONBLANK ( vKeepColData, TRUE () )
RETURN
'Table1 (2)'[Data_Col] - vFirstValue