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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.