Create a status in table A based on table B

[Arkhos94]

I have 2 table to evaluate the legal conformity of my company :

One is the list of every text send by my legal conformity supplier (named text_list) :

Text ID	Text name
TXA001	A
TXA002	B
TXA003	C
TXA004	D

And a second table with the text evaluation for each site (named evaluation) :

Text ID	Site ID	Evaluation
TXA001	SiteA	OK
TXA001	SiteB	Not OK
TXA001	SiteC	To be evaluated
TXA002	SiteA	Not applicable
TXA002	SiteB	Ok
TXA002	SiteC	Ok

(FYI : real text_list is 200 text long and evaluation is more than 2000 evaluation long)

The evaluation of the text is based on the folowing rules :

if at least 1 evaluation is "to be evaluated" => text evaluation is "to be evaluated", if not :

if all evaluation are "not applicable" => text evaluation is "not applicable", if not

if at least 1 evaluation is "not Ok" => text evaluation is "not Ok", if not "Ok"

I want to make a set of measure that allows me to count the number of Text for each status (so I can make some pie chart)

I manage to make one to calculate the status of the text but now I'm stuck : 

Nb evaluation= count('Evaluation'[Site ID])

Statut = if(calculate([Nb evaluation],'Evaluation'[Evaluation]="to be evaluated")>0,"to be evaluated",

if(calculate([Nb evaluation],'Evaluation'[Evaluation]="not applicable")=[Nb evaluation],"not applicable",

if(calculate([Nb evaluation],'Evaluation'[Evaluation]="Not Ok")>0,"Not Ok","Ok")))

Any idea on how to proceed from there ?

[v-stephen-msft · Accepted solution]

Hi @Arkhos94 ,

You may try

Measure = var _tbe=CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table','Table'[Text ID]),FILTER('Table (2)',[Evaluation]="To be evaluated"))
var _na=CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table','Table'[Text ID]),FILTER('Table (2)',[Evaluation]="not applicable"))
var _nok=CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table','Table'[Text ID]),FILTER('Table (2)',[Evaluation]="Not OK"))
var _count=CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table','Table'[Text ID]))

return IF(ISBLANK(_count),BLANK(),IF(_tbe>=1,"to be evaluated",IF(_na=_count,"not applicable",IF(_nok>=1,"not Ok","Ok"))))

The relationship is this

You can check more details from the attachment.

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.

[Arkhos94]

hello @amitchandak  and thanks for the help (again). Your proposal is good and I was very interesting to take time to understand it but it doesn't fit my need.

I cannot use a new column and have to make it a measure.

If I make it a column, the text status will be calculated based on all sites evaluation even If I filter some of them.

I have 17 differents sites, separated in 4 areas. I need it to be a measure so I can see the text status for one site only or multiple sites (when I need a whole area status) or all of them. This is why I went for a measure. Sorry for not writing it in my first message.

[amitchandak]

@Arkhos94 , Try a new column like

New column =
var _1 = countx(filter(Table2, Table1[Text ID] = Table2[Text ID] && Table2[Evaluation] = "To be evaluated"),Table2[Text ID])
var _2 = countx(filter(Table2, Table1[Text ID] = Table2[Text ID] && Table2[Evaluation] = "not applicable"),Table2[Text ID])
var _3 = countx(filter(Table2, Table1[Text ID] = Table2[Text ID] && Table2[Evaluation] = "Not OK"),Table2[Text ID])
var _4 = countx(filter(Table2, Table1[Text ID] = Table2[Text ID] ),Table2[Text ID])
return
Switch( true() ,
not(isblank(_1)) , "To be evaluated" ,
not(isblank(_2)) && _2 = _4 , "not applicable" ,
not(isblank(_3)) , "Not OK"
)