Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Geradav
Responsive Resident
Responsive Resident

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

View solution in original post

10 REPLIES 10
Geradav
Responsive Resident
Responsive Resident

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

And here is the result

Annotation 2019-08-03 183301.jpg

Let us know if that works for you

 

David

Geradav
Responsive Resident
Responsive Resident

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
Not applicable

@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
Responsive Resident
Responsive Resident

@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
Anonymous
Not applicable

@Geradav ,

Works great, Thanks!!

Anonymous
Not applicable

@Geradav ,

I have run into another issue with a real dataset I am working with. 

I need to sum the data_col before I can subract the "Test ID" from the "Test ID to Ref".

In the current configuration it is being averaged.

 

Any thoughts?

 

Thanks.

Anonymous
Not applicable

@Geradav , @Anonymous ,

Thanks for the help.

This is what I have been trying to accomplish!

Anonymous
Not applicable

@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

 

Anonymous
Not applicable

@Anonymous ,

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.

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.