Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
@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
@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
@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 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 ,
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |