How to Capture Individual Numbers in Dynamic Arrays per Cell

[nidquival]

Good day,

I would like to ask if Power BI can capture the individual IDs in an entry with a dynamic count of ID values in an entry:

Operator	Shift
1	1
1,2	2
1,3,5	1
2	2
5,4,6,7,8	1
3,1,4,2	2

The operator table has a varying number of values inside a cell that need to be individually checked and captured. This is because each number represents a specific name. For example,

OperatorID	OperatorFirstName	OperatorLastName
1	Reggie	Andrews
2	Donald	Banks
3	John	Ignatius
4	Peter	Jenkins
5	Andrew	Ports
6	David	Snow
7	Michael	Anders
8	Matthew	Lewis

The end goal is to capture the operator names and number of operators on a given shift.

Thank you,

Nico

[nidquival]

I tried splitting the values, but being in DirectQuery mode, I was unable to do so. What we did instead was we separated the Operators into multiple tables. Here is the schema of the tables:

The mainID of the Main table has active one-to-one connections with the different Operator[x] tables. As such, I cannot create active many-to-one connections from the different Operator[x] tables using the OperatorID to the Operator table. Currently, the relationships are set to inactive, and I am supposed to filter out the name of the specifically selected operator.

Is there a way by DAX for me to filter the said table as I am using Directquery? 

Thanks,

Nico 

[v-stephen-msft]

Hi @nidquival ,

Not clear about the expected result you want. Can you provide the expected result?

Best Regards,

Stephen Tao

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

[amitchandak]

@nidquival , One option it split the first column of the first table into rows in power query

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[nidquival]

@amitchandak, can this be done automatically using power query? The values will be coming from a database so the transactions will be constantly appended.