Forum Discussion
Report building (DAX)
Hi all,
I have two tables in the data field. Both are similar. But I need to make a note if anything changed.
For eg:
I have my two tables which are Table1 and Table2.
Table1
| Partno | code |
1 | A |
| 2 | B |
| 3 | C |
| 4 | B |
Table2
| Partno | code |
1 | B |
| 2 | A |
| 3 | C |
| 4 | B |
In the above tables, for the same partno, codes are changed. For eg: Partno 1 was A and now B.
I would like to know the number of partnos which have a change of code from table1 to table2.
Can I please know how to do that?
Any help is appreciated!
Thank you!
Megha
Two measures:
Changed = var _SelP = SELECTEDVALUE(Table1[Partno]) var _SelCode = SELECTEDVALUE(Table1[code]) var _selCodeInT2 = LOOKUPVALUE(Table2[code], Table2[Partno], _SelP) return if ( HASONEVALUE(Table1[Partno]) , if ( _SelCode = _selCodeInT2 , "Same Value", "Value Changed"), blank())Count Changed = Coalesce(CALCULATE( count(Table1[Partno]), FILTER(Table1, [Changed] = "Value Changed")), 0)Note: I did as two measures, you can club as one too
Results as:
3 Replies
sevenhillsSuper User
Two measures:
Changed = var _SelP = SELECTEDVALUE(Table1[Partno]) var _SelCode = SELECTEDVALUE(Table1[code]) var _selCodeInT2 = LOOKUPVALUE(Table2[code], Table2[Partno], _SelP) return if ( HASONEVALUE(Table1[Partno]) , if ( _SelCode = _selCodeInT2 , "Same Value", "Value Changed"), blank())Count Changed = Coalesce(CALCULATE( count(Table1[Partno]), FILTER(Table1, [Changed] = "Value Changed")), 0)Note: I did as two measures, you can club as one too
Results as:
Thank you! sevenhills
- VahidDM
HI Anonymous
If you just want a number that indicate the total changes, you can use this measure code:
Change = Var _A = EXCEPT(Table2,Table1) return COUNTROWS(_A)Output:
You can create a new table with below code to just find the items that changed in Table 2 in compare to table 1:
Table = EXCEPT(Table2,table1)Output:
Sample file atached.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
