Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ahead4097
Helper I
Helper I

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!
1 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)

callidcountcolumn.jpg

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

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.

callidcount.jpg

 

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

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)

callidcountcolumn.jpg

This is perfect, thank you!

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors