Measure to identify based on criteria

Results 1 to 5 of 5

Thread: Measure to identify based on criteria

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Measure to identify based on criteria


    Measure to identify based on criteria
    I have a table in powerpivot showing invoices, and two calculated columns to identify Product A (1) or Product B (2)

    Invoice Product Units Price Calculated Column 1 Calculated Column 2
    100 A 1 100 True False
    100 B 1 50 False True
    101 C 1 100 False False
    101 A 1 100 True False
    101 D 1 25 False False
    102 B 1 50 False True
    102 A 1 100 True False
    102 C 1 100 False False

    I want to be able to write a measure that will identify if each invoice has both "True" in calculated columns 1 and 2.

    That way when I pivot it out, I can see the invoice number, products bought, total spend, and if a true/false if the invoice has both products A and B.

    Any Suggestions?

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    283
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Measure to identify based on criteria

    Code:
    =IF(
        CONTAINS (
            Table, 
            Table[Product],
            "A"
        ) &&
        CONTAINS (
            Table, 
            Table[Product],
            "B"
        ),
        "Yes",
        "No"
    )
    Last edited by gazpage; Dec 7th, 2017 at 05:21 PM.

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Measure to identify based on criteria

    Thanks this works exactly how I asked!

    I unfortunately now know I forgot to include details to my question because it did not work they way I wanted.

    So the measure works perfectly if I pivot based on invoice number.

    The problem, is that I need to get down to the products bought as well. But if I add products to the pivot table, the "Criteria" measure shows No for everything.

    Is there a way to include products to the table, but the criteria only answer on the invoice level?

  4. #4
    Board Regular
    Join Date
    Apr 2015
    Posts
    283
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Measure to identify based on criteria

    Wrap the previous measure in a CALCULATE with second parameter as an ALLEXCEPT on Invoice.

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Measure to identify based on criteria

      
    Sorry I took so long to reply, this works perfectly!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com