Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Filtering by two conditions

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.

Need Help
  • vanessafvg's avatar
    vanessafvg
    9 years ago

    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's avatar
    v-jiascu-msft
    9 years ago

    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

9 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        vanessafvg
        Community Champion
        9 years ago

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