Creating a column which values are found by other columns

[Anonymous]

Hello, trying to make report for our invoice approval system. The data report doesn't include the department to which invoice is related, but I have that info seperately and by that I can find information which is the dep. related.

Current data set:

Invoice No	Confirmer	Dep
AA1	Tom	?
AA1	John	?
AA1	Maria	?
AA2	Jack	?
AA2	Albert	?
AA2	David	?
BB1	Jack	?
BB1	Maria	?
BB1	David	?

Information about the chain of approvers:

DEP	Confirmer 1	Confirmer 2	Confirmer3
Sales	Tom	John	Maria
Finance	Jack	Albert	David
HR	Jack	Maria	David

End product should look like:

Invoice No	Confirmer	Dep
AA1	Tom	Sales
AA1	John	Sales
AA1	Maria	Sales
AA2	Jack	Finance
AA2	Albert	Finance
AA2	David	Finance
BB1	Jack	HR
BB1	Maria	HR
BB1	David	HR

I have an idea how this should be done logically, but not technically.

Example:

if AA1 invoice has Tom as a confirmer it must be Sales dep.

If invoice has Jack as first confirmer and the chain also includes Albert it must be related to Finance dep.

And if it has Jack, but has Maria aswell it must be related to HR dep.

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

>> I have an idea how this should be done logically, but not technically.

Do you mean you want to get the expected value without writing code?

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hello, I mean that I kind of know how the logic should be, but when I try to write the code it doesn't work. So just wanted ideas which DAX are the best for case like this and general knowledge input into the problem

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for your reply.

you can try this measure

Measure = 
var _t1= SELECTCOLUMNS( FILTER(ALL('Table'),'Table'[Invoice No]=MIN('Table'[Invoice No])),"name",[Confirmer])
var _sales= VALUES(chain[Sales])
var _hr= VALUES(chain[HR])
var _finance= VALUES(chain[Finance])
return  SWITCH(TRUE(),
COUNTROWS(INTERSECT(_t1,_sales))=3,"sales",
COUNTROWS(INTERSECT(_t1,_hr))=3,"hr",
COUNTROWS(INTERSECT(_t1,_finance))=3,"finance")

Best Regards,

Community Support Team _Tang

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