Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

IF Statement Based on Multiple Columns

Greetings,

I have dashboard that has thousands of purchase orders with our suppliers and in some cases they tied the purchase order to the contract number and in some cases they didnt. I'm trying to write an IF Statement that will help me filter to only show purchase orders that do not have a contract number (unknown) in sample data, but there is a contract for that supplier. In the example, the yellow highlighted values show the company has 2 orders with contracts. The green highlighted value has Unknown contract. For this If Statement, I would expect only the green highligted row to show because there is a contract in place, it just wasn't used for this order. Do I need some type of helper column? How can I make this work?

 

 

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    Here a suggestion with a measure that you can use in the filter pane

     

     

    Here the DAX for the measure:

    FilterMeasure = 
VAR _helpTable1 = 
    SUMMARIZE ( 
        Table57,
        Table57[Vendor Name],
        "countContractsNotUnknown", CALCULATE ( COUNTROWS ( Table57 ), Table57[Contract Number] <> "unknown" ),
        "countContractsUnknown",    CALCULATE ( COUNTROWS ( Table57 ), Table57[Contract Number] =  "unknown" )
    )
VAR _helpTable2 =
SUMMARIZE (
    FILTER ( _helpTable1, [countContractsNotUnknown] >= 1 && [countContractsUnknown] >= 1 ) ,
    [Vendor Name]
)
RETURN 
    CALCULATE ( 
        COUNTROWS ( 
            FILTER ( 
                Table57, Table57[Contract Number] = "unknown" && 
                Table57[Vendor Name] in ( _helpTable2 ) 
            ) 
        ) 
    )

     

    You could also use the following approach:

     

    Here the DAX for FilterMeasure2:

    FilterMeasure2 = 
VAR _helpTable1 = 
    SUMMARIZE ( 
        Table57,
        Table57[Vendor Name],
        "countContractsNotUnknown", CALCULATE ( COUNTROWS ( Table57 ), Table57[Contract Number] <> "unknown" ),
        "countContractsUnknown",    CALCULATE ( COUNTROWS ( Table57 ), Table57[Contract Number] =  "unknown" )
    )
RETURN 
    COUNTROWS ( 
    FILTER ( _helpTable1, [countContractsNotUnknown] >= 1 && [countContractsUnknown] >= 1 )

     

    Here, the FilterMeasure itself is easier, but you would need to apply an additional filter on the visual to just get the ones that are unknown. On the other hand, you can direclty use that measure to also find out what the other contracts are (see graph in the middle)

     

    Hope this helps 🙂 And let me know if it does!

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

    • rks's avatar
      rks
      Icon for Resolver II rankResolver II
      4 years ago

      It'd try this (untested):

       

      Measure =
      VAR _ListOfSupplies  = CALCULATETABLE ( VALUES(Vendor_Name), Contract_Number <> "Unknown") // gives you a list of all suppliers with orders other than unknown

      RETURN

      CALCULATE (
      COUNTROWS(),
      __ListOfSupplies, //count only those rows with suppliers from above

      Contract_Number = "Unknown" //which also have an unknown number
      )

       

      Now build a diagram (or table) and drop this measure inside. When a user clicks on a bar, show details from above.