IF Statement Based on Multiple Columns

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/