Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I have to get the individual value of an id where as I am getting sum when I am joining the tables.
ID | $ | Margin |
9223 | $1,097,939.35 | $2,156,176.14 |
9230 | $54,829.31 | $1,746,478.98 |
9231 | $165,189.54 | $2,416,286.43 |
9232 | $11,209.56 | $1,063,664.75 |
9233 | $3,054.97 | $242,566.94 |
9234 | $979,928.18 | $1,904,609.81 |
9235 | $7,898.57 | $0.00 |
9236 | $13,517.66 | $302,949.23 |
9237 | $995.80 | $276,496.64 |
9238 | $1,576.19 | $317,147.18 |
Expected $ in below table
Dept Id | Accnt | $ | Margin | Expected Value | ||
9223 | Cost of Revenue | 6587636.1 | -715905 | TRUE | 1097939 | 1097939 |
9223 | Operating Revenue | 8783514.8 | 2872081 | FALSE | FALSE | |
9230 | Cost of Revenue | 603122.41 | -2180063 | TRUE | 54829.31 | 54829.31 |
9230 | Operating Revenue | 548293.1 | 3926542 | FALSE | FALSE | |
9231 | Cost of Revenue | 1486705.86 | -485937 | TRUE | 165189.5 | 165189.5 |
9231 | Operating Revenue | 2147464.02 | 2902224 | FALSE | FALSE | |
9232 | Cost of Revenue | 89676.48 | -863787 | TRUE | 11209.56 | 11209.56 |
9232 | Operating Revenue | 78466.92 | 1927451 | FALSE | FALSE | |
9233 | Cost of Revenue | 15274.85 | -3677265 | TRUE | 3054.97 | 3054.97 |
9233 | Operating Revenue | 18329.82 | 3919832 | FALSE | FALSE | |
9234 | Cost of Revenue | 6859497.26 | -1317367 | TRUE | 979928.2 | 979928.2 |
9234 | Operating Revenue | 8819353.62 | 3221976 | FALSE | FALSE | |
9235 | Cost of Revenue | 31594.28 | -5467.76 | TRUE | 7898.57 | 7898.57 |
9235 | Operating Revenue | 15797.14 | 5467.76 | FALSE | FALSE | |
9236 | Cost of Revenue | 148694.26 | -806623 | TRUE | 13517.66 | 13517.66 |
9236 | Operating Revenue | 148694.26 | 1109572 | FALSE | FALSE | |
9237 | Cost of Revenue | 5974.8 | -649260 | TRUE | 995.8 | 995.8 |
9237 | Operating Revenue | 5974.8 | 925756.2 | FALSE | FALSE | |
9238 | Cost of Revenue | 9457.14 | -2886023 | TRUE | 1576.19 | 1576.19 |
9238 | Operating Revenue | 9457.14 | 3203170 | FALSE | FALSE | |
9239 | Cost of Revenue | -1840396 | TRUE | #N/A | ||
9239 | Operating Revenue | 1884461 | FALSE | FALSE |
Solved! Go to Solution.
Hi @sekharsahu25 ,
According to your expected output,
If so, please try:
Duplicate = IF([Accnt]="Cost of Revenue",TRUE(),FALSE())
$ =
var _look=CONVERT( LOOKUPVALUE('Table 1'[$],[Dept ID],[Dept Id]) ,STRING)
return IF( [Accnt]="Cost of Revenue","FALSE", IF( _look=BLANK(),"#N/A",_look))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sekharsahu25 ,
P;ease try:
Duplicate =
var _count=CALCULATE(COUNTROWS('Table 2'),ALLEXCEPT('Table 2','Table 2'[Dept Id]))
var _rank=RANKX(FILTER('Table 2',[Dept Id]=EARLIER('Table 2'[Dept Id])),[Accnt],,ASC)
return IF(_count>1 && _rank=1,TRUE())
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sekharsahu25 ,
P;ease try:
Duplicate =
var _count=CALCULATE(COUNTROWS('Table 2'),ALLEXCEPT('Table 2','Table 2'[Dept Id]))
var _rank=RANKX(FILTER('Table 2',[Dept Id]=EARLIER('Table 2'[Dept Id])),[Accnt],,ASC)
return IF(_count>1 && _rank=1,TRUE())
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you sooooooo much ......You saved my day......
Hi @sekharsahu25 ,
According to your expected output,
If so, please try:
Duplicate = IF([Accnt]="Cost of Revenue",TRUE(),FALSE())
$ =
var _look=CONVERT( LOOKUPVALUE('Table 1'[$],[Dept ID],[Dept Id]) ,STRING)
return IF( [Accnt]="Cost of Revenue","FALSE", IF( _look=BLANK(),"#N/A",_look))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Eyelyn,
The scenario is exactly the same but I have taken the Dept Id as the condition for true and false ,not the accnt Id.
If dept Id is reflecting more than once it should be false
9223 : True
9223 : False
Based on the above I should do look up.Remaining all are same steps as you did.
Thank you so much for your response anyways.Its so much helpful and helped me a lot!!!!
Your request is unclear. What are the two tables you need to merge and what is the expected outcome?
Proud to be a Super User!
Paul on Linkedin.
Hi ,
Thanks for the response
Table 1
Dept ID | $ | Margin |
9223 | $1,097,939.35 | $2,156,176.14 |
9230 | $54,829.31 | $1,746,478.98 |
9231 | $165,189.54 | $2,416,286.43 |
9232 | $11,209.56 | $1,063,664.75 |
9233 | $3,054.97 | $242,566.94 |
9234 | $979,928.18 | $1,904,609.81 |
9235 | $7,898.57 | $0.00 |
9236 | $13,517.66 | $302,949.23 |
9237 | $995.80 | $276,496.64 |
9238 | $1,576.19 | $317,147.18 |
Table 2:
Dept Id | Accnt | Expense | COR |
9223 | Cost of Revenue | 6587636 | -715905 |
9223 | Operating Revenue | 8783515 | 2872081 |
9230 | Cost of Revenue | 603122.4 | -2180063 |
9230 | Operating Revenue | 548293.1 | 3926542 |
9231 | Cost of Revenue | 1486706 | -485937 |
9231 | Operating Revenue | 2147464 | 2902224 |
9232 | Cost of Revenue | 89676.48 | -863787 |
9232 | Operating Revenue | 78466.92 | 1927451 |
9233 | Cost of Revenue | 15274.85 | -3677265 |
9233 | Operating Revenue | 18329.82 | 3919832 |
9234 | Cost of Revenue | 6859497 | -1317367 |
9234 | Operating Revenue | 8819354 | 3221976 |
9235 | Cost of Revenue | 31594.28 | -5467.76 |
9235 | Operating Revenue | 15797.14 | 5467.76 |
9236 | Cost of Revenue | 148694.3 | -806623 |
9236 | Operating Revenue | 148694.3 | 1109572 |
9237 | Cost of Revenue | 5974.8 | -649260 |
9237 | Operating Revenue | 5974.8 | 925756.2 |
9238 | Cost of Revenue | 9457.14 | -2886023 |
9238 | Operating Revenue | 9457.14 | 3203170 |
9239 | Cost of Revenue | -1840396 | |
9239 | Operating Revenue | 1884461 |
Now I have to get table 1 $ id column in table 2 without getting duplicated.Means only one time it should get mapped in table 2 based on the Dept Id something similar like below. I created the duplicate column for my purpose.You can ignore it.
Dept Id | Accnt | Expense | COR | Duplicate | $ |
9223 | Cost of Revenue | 6587636 | -715905 | TRUE | 1097939 |
9223 | Operating Revenue | 8783515 | 2872081 | FALSE | FALSE |
9230 | Cost of Revenue | 603122.4 | -2180063 | TRUE | 54829.31 |
9230 | Operating Revenue | 548293.1 | 3926542 | FALSE | FALSE |
9231 | Cost of Revenue | 1486706 | -485937 | TRUE | 165189.5 |
9231 | Operating Revenue | 2147464 | 2902224 | FALSE | FALSE |
9232 | Cost of Revenue | 89676.48 | -863787 | TRUE | 11209.56 |
9232 | Operating Revenue | 78466.92 | 1927451 | FALSE | FALSE |
9233 | Cost of Revenue | 15274.85 | -3677265 | TRUE | 3054.97 |
9233 | Operating Revenue | 18329.82 | 3919832 | FALSE | FALSE |
9234 | Cost of Revenue | 6859497 | -1317367 | TRUE | 979928.2 |
9234 | Operating Revenue | 8819354 | 3221976 | FALSE | FALSE |
9235 | Cost of Revenue | 31594.28 | -5467.76 | TRUE | 7898.57 |
9235 | Operating Revenue | 15797.14 | 5467.76 | FALSE | FALSE |
9236 | Cost of Revenue | 148694.3 | -806623 | TRUE | 13517.66 |
9236 | Operating Revenue | 148694.3 | 1109572 | FALSE | FALSE |
9237 | Cost of Revenue | 5974.8 | -649260 | TRUE | 995.8 |
9237 | Operating Revenue | 5974.8 | 925756.2 | FALSE | FALSE |
9238 | Cost of Revenue | 9457.14 | -2886023 | TRUE | 1576.19 |
9238 | Operating Revenue | 9457.14 | 3203170 | FALSE | FALSE |
9239 | Cost of Revenue | -1840396 | TRUE | #N/A | |
9239 | Operating Revenue | 1884461 | FALSE | FALSE |
Can anyone help on the above problem where I just need only look up value and the remaining should be blank
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |