Help Calculation With filter

[koray]

Hello,

I want to calculate the number of different "Lotno" that has each different "product no" and ending with the letters "G" and "S".

As an example, the calculation result of the above table should be 3.

Thank you,

Koray

[FrankAT]

Hi @koray 

what about the following solution:

Number of Lotno G or S = 
CALCULATE(
    COUNTROWS('Table'), 
    RIGHT('Table'[Lotno],1) = "S" || RIGHT('Table'[Lotno],1) = "G"
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[koray]

Hi @FrankAT 

It's worked thank you 🙂 But how can we do the calculation in a unique way.
In addition, I want to calculate the number of different "Lotno" that each different "product no" has.
If batch no, depending on the same product no, the result should be 1. The following table does not count as colored red.

[FrankAT]

Hi @koray 

sorry I can't follow your logic. This it what I get:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[koray]

Hi @FrankAT 

Thank you for reply. The calculation result should be 3. Because there is one more of the product no and batch no, which are marked in Red. I want it to compare product no with batch no and calculate unique ones.

[v-yiruan-msft]

Hi @koray ,

Please update the formula of measure which @FrankAT  provided as below and check whether you can get the desired result:

Count of Lotno with letter G or S = CALCULATE(COUNTROWS(DISTINCT('Products')), RIGHT('Products'[Lotno],1) = "S" || RIGHT('Products'[Lotno],1) = "G")

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[koray]

Hi @v-yiruan-msft 

Thank you for reply. calculated the result as 2 😞 

I think it calculated as 1 because 2 same lot numbers are the same. But I want it to calculate as 2 because the product numbers are different.

[v-yiruan-msft · Accepted solution]

Hi @koray ,

I just created a sample pbix file and update the formula of measure(see attachment), please check whether that is what you want. If no, please provide more sample data and your desired result. Thank you.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Pragati11]

Hi @koray ,

Can you paste a screenshot around the expected output table?

Thanks,

Pragati

[koray]

Hi @Pragati11 
I think there is a problem with the forum. The picture should be  visible, now.

[Pragati11]

Hi @koray ,

I still don't see the output picture.

Thanks,

Pragati

[koray]

@Pragati11 

Oh, sorry. No need for a output picture. I want to make a calculation, the result of the output will be 3.