Filtering by two conditions

[Anonymous]

Dear all,

I have a table like below:

Invoice No               Product Code           Value

50                             101                            200

50                             103                            550

50                             104                            300

51                             103                            500

51                             105                            600

52                             102                            200

53                             103                            400

54                             101                            650

54                             102                            300

54                             103                            200

55                             102                          1200

55                             103                          1000

Here, Total value of invoice no 50 = 1050 (200+550+300)

                                                 51 =  1100

                                                 52 = 200

                                                 53 = 400

                                                 54 = 1150

                                                 55 = 2200

I want to count the number of distinct invoices with total bill value between 1000 - 2000 and containing product 101 or 102 or both.

Here the total number of invoices with bill value between 1000- 2000 is 3 (i.e. 50, 51 and 54)

From this, two bills contain product 101 or 102 or both. (i.e. 50 and 54)

Therefore the number of invoices with total bill value between 1000-2000 and containing product 101 or 102 or both is 2.

How can I get this number? Please help. Thank you.

[vanessafvg · Accepted solution]

@Anonymous from what you can see the measure does actually work however it expects some sort of context, 

for example if you place it on a matrix you would see that the value woudl be summed by invoice number and measure test would show 1 and 1 for invoice 50 and 54

so to use it on a card, i think you actually need to summarise this in a table

try this

Table Test =
SUMMARIZE (
Table1,
Table1[Invoice No],
"Measure Test", CALCULATE (
DISTINCTCOUNT ( Table1[Invoice No] ),
FILTER (
Table1,
SUM ( Table1[Value] ) >= 1000
&& SUM ( Table1[Value] ) <= 2000
&& Table1[Product Code] IN { "101", "102" }
)
)
)

[v-jiascu-msft · Accepted solution]

@Anonymous

Hi,

@vanessafvg ‘s formula is wonderful. I just add a few parts. Try this measure please. This can be used in a card visual.

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[Invoice No],
            "Measure Test", CALCULATE (
                DISTINCTCOUNT ( Table1[Invoice No] ),
                FILTER (
                    Table1,
                    SUM ( Table1[Value] ) >= 1000
                        && SUM ( Table1[Value] ) <= 2000
                        && Table1[Product Code] IN { 101, 102 }
                )
            )
        ),
        [Measure Test] = 1
    )
)

Best Regards!

Dale

[Anonymous]

@vanessafvg & @v-jiascu-msft Thank you very much to both of you!

Your support is highly appreciated.

[vanessafvg]

@Anonymous  

Measure Test =
CALCULATE (
    DISTINCTCOUNT ( Table1[Invoice No] ),
    FILTER (
        Table1,
        SUM ( Table1[Value] ) >= 1000
            && SUM ( Table1[Value] ) <= 2000
            && Table1[Product Code] IN { "101", "102" }
    )
)

[Anonymous]

Dear @vanessafvg,

From your function, I get blank as my output. But they don't give any error for this. I can't recognize the mistake.

Please help

Thank you

[vanessafvg]

@Anonymous please post how you wrote your measure from what i gave you?  also please post screenshot of results

[Anonymous]

@vanessafvg

 

[vanessafvg]

@Anonymous and if you place it in your table?

[Anonymous]

@vanessafvg

Now the output is like below:

[vanessafvg · Accepted solution]

@Anonymous from what you can see the measure does actually work however it expects some sort of context, 

for example if you place it on a matrix you would see that the value woudl be summed by invoice number and measure test would show 1 and 1 for invoice 50 and 54

so to use it on a card, i think you actually need to summarise this in a table

try this

Table Test =
SUMMARIZE (
Table1,
Table1[Invoice No],
"Measure Test", CALCULATE (
DISTINCTCOUNT ( Table1[Invoice No] ),
FILTER (
Table1,
SUM ( Table1[Value] ) >= 1000
&& SUM ( Table1[Value] ) <= 2000
&& Table1[Product Code] IN { "101", "102" }
)
)
)