Multiple Criteria Lookup

[Ahead4097]

Hello all, I'm pretty new to Power BI, and I'm needing some help to figure out the best way to get a result.
The columns I'm working with are CALL ID, STORE #, and VISIT DATE.
I need to find a function that can look at CALL ID and say "check the CALL ID. If another CALL ID exists in the same store on the same day, return 1. But if that's the only CALL ID in that store for that day, return 2."
The CALL ID is a unique number to every call, but the STORE # and VISIT DATE can be duplicated.

Is this possible? Thanks!

[jdbuchanan71]

Hello @Ahead4097 

The following measure will return the result you are looking for, you will just have to change the name of your table.

Measure = 
VAR StoreNum = VALUES('Table'[STORE #] )
VAR VisitDate = VALUES('Table'[VISIT DATE] )
VAR CallCount = 
    CALCULATE(
        COUNTROWS ( 'Table' ),
        ALL ( 'Table'[CALL ID] )
        ,StoreNum
        ,VisitDate
    )
RETURN
IF ( CallCount = 1, 2, 1)

I also uploaded my testing .pbix so you can take a look it it: https://www.dropbox.com/s/jyoxwrae8rmz5y8/CallIDCount.pbix?dl=0

The RowCount measure just counts the rows in 'Table'.  If we look at a STORE # and VISIT DATE pair we can see the ones that have more than 1 CALL ID.  Further, if you click on a line in the first visual it will filter the second visual to show just the CALL ID that hit in that STORE # on that VISIT DATE.

[Ahead4097]

Is there a way to do this measure in Power Query Editor?

[jdbuchanan71 · Accepted solution]

I'm not sure but I am guessing you want it as a column so you can put it in a slicer?  If so, you can add a calculated column to your table that will give you the same result:

Column = 
VAR StoreNum = 'Table'[STORE #]
VAR VisitDate = 'Table'[VISIT DATE]
VAR CallCount = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER ( 
            'Table',
            'Table'[STORE #] = StoreNum &&
            'Table'[VISIT DATE] = VisitDate
        )
    )
RETURN IF ( CallCount = 1, 2, 1)

[Ahead4097]

This is perfect, thank you!

[Ahead4097]

Thanks so much! I'll give it a shot tomorrow.