cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wayers Member
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

TestDif1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Geradav Regular Visitor
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

View solution in original post

10 REPLIES 10
Highlighted
bestmbaman Member
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

 

wayers Member
Member

Re: DAX Help - Measure to find difference

@bestmbaman ,

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.

 

bestmbaman Member
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.

Geradav Regular Visitor
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

Annotation 2019-08-03 183301.jpg

Let us know if that works for you

 

David

Geradav Regular Visitor
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

View solution in original post

wayers Member
Member

Re: DAX Help - Measure to find difference

@Geradav , @bestmbaman ,

Thanks for the help.

This is what I have been trying to accomplish!

wayers Member
Member

Re: DAX Help - Measure to find difference

@Geradav ,

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

 

diff1.PNG

 

Geradav Regular Visitor
Regular Visitor

Re: DAX Help - Measure to find difference

@wayers 

 

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

Re: DAX Help - Measure to find difference

@Geradav ,

Works great, Thanks!!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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