Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I would like to thank you all for your help. I am a rookie in PBI and you are saving my life vey often (I would not have enough money to pay all the beers I should pay XD) . Unfortunately I am blocked with the current situation:
I need to compare two different tables from two different merges (T1 and T2), and add a column in T1 (or a similar strategy, please do not hesitate if needed...).
This T1 will give values of the T2 if some match takes place. Easier with an Example:
1. I need to check if ID2 of T2 is present in the ID.1 of T1
2. IF yes, I need to fill the T1 VALUE T1 with the value of T2. VALUE T2. When the relationship is 1 to 1 there is no problem so marked as (OK) BUT.
3. IF several IDs are present in T2, I need to supply the one which correspond to the ID which date is bigger (see columns VALUE T1 and T2) marked as (!)
T1 | T1 | T1 | T2 | T2 | T2 | ||
ID.1 | DATE.1 | VALUE T1 | X | ID.2 | DATE.T2 | VALUE T2 | |
1 | 2017 | A (OK) | X | 1 | 2018 | A | |
2 | 2016 | B (OK) | X | 2 | 2019 | B | |
2 | 2017 | B (OK) | X | 3 | 2019 | C | |
3 | 2016 | C (!) | X | 3 | 2020 | D | |
3 | 2018 | C (!) | X | ||||
3 | 2020 | D (!) | X | ||||
4 | 2019 | null (!) | X |
To solve it with Excel would be something like (using a matrix form):
VALUET1 = INDEX(VALUET2:VALUET2;MAX(MATCH(2;1/((ID.2:ID.2=ID.1.1)*(DATE.T2:DATE.T2<=DATE.T1)));1))
I have tried to merge both tables as well, but my PC struggles. When I do the data crossing the results are too big (obviously this is a simple case) an I can have 8 or 9 DATES for the same ID and thousands of IDs..
Any help please?
Hi @JOSERB ,
I modified @camargos88 's expression. Please check:
1. Create relationship between Table 1 and Table 2.
2. Create a column like so:
Column =
VAR _date =
CALCULATE (
MIN ( 'Table 2'[DATE.T2] ),
FILTER (
'Table 2',
'Table 2'[ID.2] = EARLIER ( 'Table 1'[ID.1] )
&& 'Table 2'[DATE.T2] >= EARLIER ( 'Table 1'[DATE.1] )
)
)
VAR result =
CALCULATE (
DISTINCT ( 'Table 2'[VALUE T2] ),
FILTER (
'Table 2',
'Table 2'[ID.2] = EARLIER ( 'Table 1'[ID.1] )
&& 'Table 2'[DATE.T2] = _date
)
)
VAR Count_ =
COUNTROWS ( RELATEDTABLE ( 'Table 2' ) )
RETURN
IF (
Count_ = 1,
result & " (OK)",
IF ( Count_ > 1, result & " (!)", "null (!)" )
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @JOSERB ,
Try this calcultated column:
it worked! thank you so much guys 🙂
@JOSERB , Create a new column in T1 like one of these
countx(filter(T1, T1[ID1] =T2[ID2] && T1[DATE.1] =T2[DATE.2]),T2[VALUE])
Or
Minx(filter(T1, T1[ID1] =T2[ID2] && T1[DATE.1] =T2[DATE.2]),T2[VALUE])
You can use for calculation
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |