Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple Criteria Lookup

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!
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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)

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Hello Anonymous 

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Thanks so much! I'll give it a shot tomorrow.
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        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)