Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sekharsahu25
Regular Visitor

Need hep

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 IdAccnt$Margin Expected Value
9223Cost of Revenue6587636.1-715905TRUE10979391097939
9223Operating Revenue8783514.82872081FALSEFALSE 
9230Cost of Revenue603122.41-2180063TRUE54829.3154829.31
9230Operating Revenue548293.13926542FALSEFALSE 
9231Cost of Revenue1486705.86-485937TRUE165189.5165189.5
9231Operating Revenue2147464.022902224FALSEFALSE 
9232Cost of Revenue89676.48-863787TRUE11209.5611209.56
9232Operating Revenue78466.921927451FALSEFALSE 
9233Cost of Revenue15274.85-3677265TRUE3054.973054.97
9233Operating Revenue18329.823919832FALSEFALSE 
9234Cost of Revenue6859497.26-1317367TRUE979928.2979928.2
9234Operating Revenue8819353.623221976FALSEFALSE 
9235Cost of Revenue31594.28-5467.76TRUE7898.577898.57
9235Operating Revenue15797.145467.76FALSEFALSE 
9236Cost of Revenue148694.26-806623TRUE13517.6613517.66
9236Operating Revenue148694.261109572FALSEFALSE 
9237Cost of Revenue5974.8-649260TRUE995.8995.8
9237Operating Revenue5974.8925756.2FALSEFALSE 
9238Cost of Revenue9457.14-2886023TRUE1576.191576.19
9238Operating Revenue9457.143203170FALSEFALSE 
9239Cost of Revenue -1840396TRUE#N/A 
9239Operating Revenue 1884461FALSEFALSE 

 

2 ACCEPTED SOLUTIONS
v-eqin-msft
Community Support
Community Support

Hi @sekharsahu25 ,

 

 According to your expected output,

  • if [Accnt]="Cost of Revenue",then TRUE() and need to get the $ from Table 1 based on each iDept id and
  • if [Accnt]="Operating Revenue", then False()

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

Eyelyn9_0-1637819510654.png

 

 

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.

View solution in original post

v-eqin-msft
Community Support
Community Support

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.

View solution in original post

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

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 ,

 

Glad to help you😊

 

Best Regards,
Eyelyn Qin

v-eqin-msft
Community Support
Community Support

Hi @sekharsahu25 ,

 

 According to your expected output,

  • if [Accnt]="Cost of Revenue",then TRUE() and need to get the $ from Table 1 based on each iDept id and
  • if [Accnt]="Operating Revenue", then False()

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

Eyelyn9_0-1637819510654.png

 

 

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

PaulDBrown
Community Champion
Community Champion

Your request is unclear. What are the two tables you need to merge and what is the expected outcome? 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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 IdAccntExpenseCOR
9223Cost of Revenue6587636-715905
9223Operating Revenue87835152872081
9230Cost of Revenue603122.4-2180063
9230Operating Revenue548293.13926542
9231Cost of Revenue1486706-485937
9231Operating Revenue21474642902224
9232Cost of Revenue89676.48-863787
9232Operating Revenue78466.921927451
9233Cost of Revenue15274.85-3677265
9233Operating Revenue18329.823919832
9234Cost of Revenue6859497-1317367
9234Operating Revenue88193543221976
9235Cost of Revenue31594.28-5467.76
9235Operating Revenue15797.145467.76
9236Cost of Revenue148694.3-806623
9236Operating Revenue148694.31109572
9237Cost of Revenue5974.8-649260
9237Operating Revenue5974.8925756.2
9238Cost of Revenue9457.14-2886023
9238Operating Revenue9457.143203170
9239Cost of Revenue -1840396
9239Operating 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 IdAccntExpenseCORDuplicate$
9223Cost of Revenue6587636-715905TRUE1097939
9223Operating Revenue87835152872081FALSEFALSE
9230Cost of Revenue603122.4-2180063TRUE54829.31
9230Operating Revenue548293.13926542FALSEFALSE
9231Cost of Revenue1486706-485937TRUE165189.5
9231Operating Revenue21474642902224FALSEFALSE
9232Cost of Revenue89676.48-863787TRUE11209.56
9232Operating Revenue78466.921927451FALSEFALSE
9233Cost of Revenue15274.85-3677265TRUE3054.97
9233Operating Revenue18329.823919832FALSEFALSE
9234Cost of Revenue6859497-1317367TRUE979928.2
9234Operating Revenue88193543221976FALSEFALSE
9235Cost of Revenue31594.28-5467.76TRUE7898.57
9235Operating Revenue15797.145467.76FALSEFALSE
9236Cost of Revenue148694.3-806623TRUE13517.66
9236Operating Revenue148694.31109572FALSEFALSE
9237Cost of Revenue5974.8-649260TRUE995.8
9237Operating Revenue5974.8925756.2FALSEFALSE
9238Cost of Revenue9457.14-2886023TRUE1576.19
9238Operating Revenue9457.143203170FALSEFALSE
9239Cost of Revenue -1840396TRUE#N/A
9239Operating Revenue 1884461FALSEFALSE

 

 

 

sekharsahu25
Regular Visitor

Can anyone help on the above problem where I just need only look up value and the remaining should be blank

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.