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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 @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.

callidcount.jpg

 

Anonymous
Not applicable

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

Anonymous
Not applicable

This is perfect, thank you!

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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