Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.