cancel
Showing results for
Did you mean:
Member

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: DAX Help - Measure to find difference

After looking at @bestmbaman , 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 10
Highlighted
Member

## Re: DAX Help - Measure to find difference

@wayers  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

Member

## Re: DAX Help - Measure to find difference

Your response is correct, I put the incorrect data in the example for Data ID 3.

Can offer some insight on how to create a new table with the data related such that I can complete finding the difference mentioned in the original post?

Thanks.

Member

## Re: DAX Help - Measure to find difference

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.

Regular Visitor

## Re: DAX Help - Measure to find difference

Hi @wayers

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] - vRefNum```

And here is the result

Let us know if that works for you

David

Regular Visitor

## Re: DAX Help - Measure to find difference

After looking at @bestmbaman , 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```
Member

## Re: DAX Help - Measure to find difference

Thanks for the help.

This is what I have been trying to accomplish!

Member

## Re: DAX Help - Measure to find difference

I have been using your formula below and it has been working well, but I have a situation with some of my data where multipule Test ID use the same Test Map number and this is causing an error of "A table of multiple values was supplied where a single value was expected."

I think I need to add HASONEVALUE to the formula but have not been able to come up with a solution.

If the Test Map is use twice in each TestID, I only need to see one of the Test Map data points returned.

Any thoughs on this?

Previous Formula

```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```

Test Map data changed and error showing

Regular Visitor

## Re: DAX Help - Measure to find difference

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```
Member

## Re: DAX Help - Measure to find difference

Works great, Thanks!!

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,349)