Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Solved! Go to 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.
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.
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.
@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"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |