DAX Help

[Anonymous]

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!!

[Anonymous]

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]

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

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

[Anonymous]

@Anonymous 

Could you share your pbix file with this example?

[Anonymous · Accepted solution]

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

[Anonymous]

@Anonymous 

Thanks for this solution!!

[Anonymous]

@Anonymous 

Thanks for providing some clarity and information on this. I used the same stratagy with my data and got the desired results!

Unfortunaly I will not be able to break my original data table into mupltipule tables. The selection for the Test_Item must be in the report (as a slicer) because analysis of the data will need to be able to choose what to compare (as many times there are hundreds of options in that column).

Can you provide an option that would not require creating multipule tables that would deliver the same end result?

Thanks!