Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Help

Looking for help with the below.

 

I have the below table

 

 

 

 

 

 

 

 

 

 

I would like to create 2 sets of data to compare

 

Data group 1 = sliced by 'TEST_ITEM' for 'A' then sliced by 'TEST_ID' for '2'

Shown below

 

Data group 2 = sliced by 'TEST_ITEM' for 'B' then sliced by 'TEST_ID' for '1'

Shown below

 

Next filter data group 1 and data group 2 to only have matching 'TEST_VAR'

This should return 'TEST_VAR' C and D

 

Last I would like to return the difference of data group 2 from data group 1, this should return

TEST_VAR

C=10 and D=0

 

Any help is appreciated!!

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous 

    I did it without splitting the table.

    Attaching the PBIX file.

    I did not QA this, sorry :)


    Enjoy!

    Let me know if the solution is OK for you.
    A

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous 

    What is 

    create 2 sets of data

    ?

    There are many ways to achieve that. What should be the final output? Visual?
    Cheers!
    A

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

      By create 2 sets of data, Im pmlying that the original table could be filtered twice to extact the data needed to create the measure i'm trying to produce. I'm going to use the result in a visual.

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous 

        Here is my solution. Might be other/better/nicer ones.

        1. Upload only the relevant data into your table(s). i.e. Table1 will include only Item=A and ID=2; Table2 will include only Item=B and ID=1.

        I achieved this in the query editor by adding steps to filter the columns.

        As mentioned above, I have 2 tables and not 1.

         

        2. Create 2 custom columns

        Equal Test Var = IF(T1[Test Var] = RELATED(T2[Test Var]),1,0)

        This ^ is to mark the identical columns by Test_Var.

        Difference = IF(T1[Test Var] = RELATED(T2[Test Var]),RELATED(T2[Results]) - T1[Results],-990)

        This ^ is to calculate the substruct between the results values.

         

        After applying the filters (Equal Test Var=1), this is what I got:

         

        Thanks!
        A