cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
caiobarbas
Regular Visitor

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: 

caiobarbas_1-1674614154576.png

 

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:

caiobarbas_2-1674614645876.png

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.

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@caiobarbas , 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

View solution in original post

FreemanZ
Community Champion
Community Champion

hi @caiobarbas 

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
)
RETURN
SWITCH(
    TRUE(),
   "Irregular" IN _list, "Irregular",
   "Absent" IN _list, "Ausente",
    "Pendente"
)

View solution in original post

v-jianboli-msft
Community Support
Community Support

Hi @caiobarbas ,

 

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:

vjianbolimsft_0-1674698525213.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

Hi @caiobarbas ,

 

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:

vjianbolimsft_0-1674698525213.png

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.

FreemanZ
Community Champion
Community Champion

hi @caiobarbas 

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
)
RETURN
SWITCH(
    TRUE(),
   "Irregular" IN _list, "Irregular",
   "Absent" IN _list, "Ausente",
    "Pendente"
)

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.

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:

 

caiobarbas_0-1674666119740.png

 

The exactly output I expected!

 

Once more: thank you very much!

amitchandak
Super User
Super User

@caiobarbas , 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:

caiobarbas_0-1674667972994.png

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.



Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.