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

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

  • 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 , Anonymous ,

      Thanks for the help.

      This is what I have been trying to accomplish!

    • 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