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 like to have a DAX complete.
Table1 has columns; Config ID, Test ID, Test ID to Reference, and Test Map
Table2 has columns; Data ID and Data
"Config ID" and "Data ID" create the relationship between the tables.
I would like to find the differnce between a selected "Test ID" compared to its "Test ID to Reference" , returning a value for each "Test Map".
In the below image (circled in red) is the measure I am looking for, "Test Difference".
The "Test Differnce" result for the first row should be -2;
"Test ID" 1, "Test Map" 10 - "Test ID" 2, "Test Map" 10
or more easily thought as;
"Data ID" 1 (23) - "Data ID" 3 (27), 23-27=-2
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
10 Replies
Anonymous I think you are not pointing to the right Data ID in your tables. As I can see from table screenshots:
Data ID 1 = 23 and Data 3 = 31 and the substraction should be -8 not -2!
If the format of your columns are all numbers and you created a new column as Data in your new table you can easilly substract them. Please clarify your problem clearly to help you solve it
If you have two tables with relationship create a new collumn and use lookup value() to easily find values. please refer to the following link for details:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
If need more help let me knkow.
If this reply solves your problem please accept it as solution to help others find the right answer.
Hi Anonymous
Here is my proposition with a calculated column
Dif = VAR vCurrentTestIDtoRef = 'Table1 (2)'[Test ID to Ref] VAR vCurrentTestMap = 'Table1 (2)'[Test Map] VAR vRefNum = CALCULATE ( SUM ( 'Table1 (2)'[Data_Col] ), FILTER ( ALL ( 'Table1 (2)' ), 'Table1 (2)'[Test ID] = vCurrentTestIDtoRef ), FILTER ( ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Test ID] ), 'Table1 (2)'[Test Map] = vCurrentTestMap ) ) RETURN 'Table1 (2)'[Data_Col] - vRefNumAnd here is the result
Let us know if that works for you
David
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