Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 lik...
Need Help
  • Geradav's avatar
    Geradav
    7 years ago

    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's avatar
Geradav
Responsive Resident
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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

 

 

  • Geradav's avatar
    Geradav
    Responsive Resident
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Geradav ,

      Works great, Thanks!!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.