Forum Discussion
Categorize data with multiple rows based on one or more values within each row
Goodnight. I'm having problems trying to do a very specific analysis within Power BI.
In this case, I have a spreadsheet in which each line represents the result of a chemical analysis conducted in several batches. Each batch can appear more than once, depending on how many analyzes have been performed on it.
In these analyses, we can receive 3 types of results: "regular", "irregular" and "absent". The same batch may have, within its set of analyses, regular, irregular or absent results.
What is my objective: I would like to create a measure or generate a table that allows me to see which batches are within or outside the regularity.
For a batch to be considered irregular, it is sufficient that only one or more entries are either irregular or missing.
Example:
In the case above, there are 3 different batches (**5531, **5624 and **5888) within 13 rows. None of them have passed the quality control:
- batch number 5531 has 1 irregular parameter;
- batch number 5624 has 2 irregular parameters; and
- batch number 5888 has 3 abscents parameters (pending results)
The output I'd like to see:
As you can see, the table I wanna get it a table witch indicates me the currently status of the whole analysed batch.
- If there's a single irregular row, the whole batch is irregular;
- If theres a single abscent row (without irregular rows), so the whole batch has pending results;
- if there are no abscent or irregular rows, the whole batch has passed the quality controll. So it's considered a regular batch.
So far, I've tried creating a calculated column (in the first table) concatenating the batch number with it's status, and then apllying a measure to distinct count it, filtering the irregular batches. But it showed not to be useful, because when I try to make the proportion of the total irregular batches in relation to the total batches, the total number does not represent the real number of batches, because the batches that present 2 or more results are also added to the count.
The only alternative I've thought being useful was making a whole new database structure, adding all the results in distinct columns,. But the database size is really big, so the task would be really hard...
If anyone there could help me with this little big problem, I'd be very grateful.
Best regards.
Anonymous , You can create a new column
Status =
var _cnt = countx(filter(Table, [Lote controle] = earlier([Lote control] ) && [Status] ="Irregular"), [Lote control])
return
Switch(True(),
isblank(_cnt) , "Pending",
"Irregular"
)
You can additional conditions
hi Anonymous
try to plot a table visual with the Lote controle column and a measure like:
Measure =VAR _batch = MAX(TableName[Lote controle])VAR _list =CALCULATETABLE(VALUES(TableName[Status]),TableName[Lote controle]=_batch)RETURNSWITCH(TRUE(),"Irregular" IN _list, "Irregular","Absent" IN _list, "Ausente","Pendente")Hi Anonymous ,
Please try:
Measure = var _Irregualr = CALCULATE(COUNT('Table'[Status]),FILTER('Table',[Status]="Irregular")) var _Ausente = CALCULATE(COUNT('Table'[Status]),FILTER('Table',[Status]="_Ausente")) return SWITCH(TRUE(), _Irregualr>0,"Irregular", _Ausente>0&&_Irregualr=0,"Ausente", "Pendente")Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous , You can create a new column
Status =
var _cnt = countx(filter(Table, [Lote controle] = earlier([Lote control] ) && [Status] ="Irregular"), [Lote control])
return
Switch(True(),
isblank(_cnt) , "Pending",
"Irregular"
)
You can additional conditions
Hey there, amitchandak ! I've just tried your alternative, and it seems to be working fine.
However, I'm not getting the "Regular" results within the batches that have passed the quality test.
("Conforme" status means "Regular")
Example:
The whole batch is regular, but I'm still getting "pending" as the column result.
As I don't know much how the functions you used work, I'm afraid I can't add the condition myself 😅
How would you add this additional case?
Best regards.
hi Anonymous
try to plot a table visual with the Lote controle column and a measure like:
Measure =VAR _batch = MAX(TableName[Lote controle])VAR _list =CALCULATETABLE(VALUES(TableName[Status]),TableName[Lote controle]=_batch)RETURNSWITCH(TRUE(),"Irregular" IN _list, "Irregular","Absent" IN _list, "Ausente","Pendente")It worked perfectly, FreemanZ . I appreciate so much! 🙏
I'll take a look on how those 3 functions work (CALCULATETABLE, VAR and SWITCH), so I may be able to code these workarounds on my own.
Thank you very much, my friend.
Here the output:
The exactly output I expected!
Once more: thank you very much!
Hey again, FreemanZ .
One more thing: although the measure you coded is working, I'm now stucked with this: how can I aplly functions like "count" or "countif" on this measure?
What I wanna do is to verify how many of those batches proportionally represent the regulars, irregulars and abscents of the total. Is there any way of doing this?
Best regards.
Hi Anonymous ,
Please try:
Measure = var _Irregualr = CALCULATE(COUNT('Table'[Status]),FILTER('Table',[Status]="Irregular")) var _Ausente = CALCULATE(COUNT('Table'[Status]),FILTER('Table',[Status]="_Ausente")) return SWITCH(TRUE(), _Irregualr>0,"Irregular", _Ausente>0&&_Irregualr=0,"Ausente", "Pendente")Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.