I want to get the list of numbers matching with one column to second column within the same table

[chiru5262]

So I have the below info

WO num	has followup up	Worktype
1	Y	P
2	Y	P
3	Y	P
4	Y	P
5	Y	P

where my wo number have the followup work as 'Y'.

Now in the same table I have the below info where the wonum which have followup work as Y will match with the 'Orginating Record' column below in the same table.

WO num	Worktype	Orginating Record
6	C	1
7	C	1
8	C	1
9	C	2
10	C	2
11	C	2
12	C	3
13	C	4
14	C	5

The output  I want to acheive

How many wonum from table 1 have related orginating record in table 2. from the above example the out put should be

WO num table 1	WO num table 2
1	6
	7
	8
2	9
	10
	11
3	12
4	13
5	14

Appreciate your help guys.

@mickey64  please see if you can help on this, appreciate your time

[bhanu_gautam · Accepted solution]

@chiru5262 , Try using updated dax with value 

OutputTable =
VAR Table1 =
SELECTCOLUMNS(
FILTER(WorkOrders, WorkOrders[followup up] = "Y"),
"WO num table 1", VALUE(WorkOrders[WO num])
)
VAR Table2 =
SELECTCOLUMNS(
FILTER(WorkOrders, NOT ISBLANK(WorkOrders[Orginating Record])),
"WO num table 1", VALUE(WorkOrders[Orginating Record]),
"WO num table 2", WorkOrders[WO num]
)
RETURN
NATURALINNERJOIN(Table1, Table2)

[Anonymous · Accepted solution]

Hi @chiru5262 

All you need to do is change your data type.

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chiru5262]

@amitchandak Could you please advise on above

[chiru5262]

@amitchandak ,@Greg_Deckler  could you guys please assist on above

[bhanu_gautam]

@chiru5262 , Create a calculated column to identify Wonum

RelatedWO =
IF (
ISBLANK ( WorkOrders[Orginating Record] ),
BLANK (),
WorkOrders[Orginating Record]
)

And then create a new output table by going to modelling and new table

OutputTable =
VAR Table1 =
SELECTCOLUMNS (
FILTER ( WorkOrders, WorkOrders[followup up] = "Y" ),
"WO num table 1", WorkOrders[WO num]
)
VAR Table2 =
SELECTCOLUMNS (
FILTER ( WorkOrders, NOT ISBLANK ( WorkOrders[Orginating Record] ) ),
"WO num table 1", WorkOrders[Orginating Record],
"WO num table 2", WorkOrders[WO num]
)
RETURN
NATURALINNERJOIN ( Table1, Table2 )

Go to the Report view.
Add a new table visual.
Drag the columns WO num table 1 and WO num table 2 from the OutputTable to the table visual.

[chiru5262]

@bhanu_gautam  its giving the below error.

Is it because instead of  WorkOrders[followup up] = "Y" I used  WorkOrders[followup up] = 1

any solution for that

[bhanu_gautam · Accepted solution]

@chiru5262 , Try using updated dax with value 

OutputTable =
VAR Table1 =
SELECTCOLUMNS(
FILTER(WorkOrders, WorkOrders[followup up] = "Y"),
"WO num table 1", VALUE(WorkOrders[WO num])
)
VAR Table2 =
SELECTCOLUMNS(
FILTER(WorkOrders, NOT ISBLANK(WorkOrders[Orginating Record])),
"WO num table 1", VALUE(WorkOrders[Orginating Record]),
"WO num table 2", WorkOrders[WO num]
)
RETURN
NATURALINNERJOIN(Table1, Table2)

[chiru5262]

@bhanu_gautam Hi Bhanu

Still getting error.

Could you please check and advise, sorry for the inconvenience any

[chiru5262]

@bhanu_gautam @rajendraongole1  could you please advise on above, appreciate for your help thanks.