Forum Discussion

Arkhos94's avatar
Arkhos94
Icon for Helper IV rankHelper IV
5 years ago
Solved

Create a status in table A based on table B

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 IDText name
TXA001A
TXA002B
TXA003C
TXA004D

 

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

Text IDSite IDEvaluation
TXA001SiteAOK
TXA001SiteBNot OK
TXA001SiteCTo be evaluated
TXA002SiteANot applicable
TXA002SiteBOk
TXA002SiteCOk

 

(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 ?
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

     

     

  • Arkhos94's avatar
    Arkhos94
    Icon for Helper IV rankHelper IV
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.