cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericsara
Helper I
Helper I

Find a single value in a table based on multiple criteria

I have tables with columns as follows;

  • Tickets
    • Ticket Number
    • Ticket Name
    • Client ID
    • Status ID
    • Type ID
  • Client
    • Client ID
    • Name
    • Address
  • Status
    • Status ID
    • Name 
    • Description
  • Type
    • Type ID
    • Name
    • Description

 

What I am looking to return is the single ticket number that exists when

  1. Client is selected via a Slicer
  2. Status = Open
  3. Type = Management

 

It is possible that this could have more than one result.  In this case, I would want N/A as there should only be one result per client. 

What DAX would I need to find the one ticket for each client?

 

Below is some example data

 

Ticket Table

Ticket NumberTicket NameClient IDStatus IDType ID
AU-4589Management - Tables R US1000OPMAN
AU-4599Seup up account1000CLTAS
NZ-5100Management - Trucks n Trucks1001OPMAN
NZ-5645Help find keys1001OPSUP
NZ-6545Management - One Sock1002CLMAN

 

Client Table

Client IDNameAddress
1000Tables R US1 Your Place
1001Trucks n Trucks22 Me Drive
1002One Sock4 Lost Place

 

Status Table

Status IDNameDescription
OPOpenTicket is active
CLClosedTicket is closed

 

Type Table

Type IDNameDescription
MANManagementTo manage the overall status of your client
TASTaskAn item to out on your to do list
SUPSupportSupport raised by the client'

 

I am looking for a DAX expression that is something like this

 

Managament Ticket Number = 

Client is (Selected from a Slicer),

Status ID is OP

Type ID is MAN

 

So if the slicer listed all three clients and I then selected each one I want a Card visual to show the ticket number as follows

Client SelectedTicket Number shown in card
Tables R USAU-4589
Trucks n TrucksNZ-5100
One SockN/A (this is because the Management Ticket for this client is Closed)

 

I hope this helps add clarity to what I am looking to acheive. 

 

Thanks, 

 

 

1 ACCEPTED SOLUTION
ericsara
Helper I
Helper I

Ended up using this DAX

 

CALCULATE ( FIRSTNONBLANK('Tickets'[Ticket Number],1),'Status'[StatusID]="Open",'Type'[TypeID]="Client Management",'Client'[ClientID]=ClientID)
 
Where ClientID is a lookup for the selected (using a slicer) client ID. 

View solution in original post

7 REPLIES 7
ericsara
Helper I
Helper I

Ended up using this DAX

 

CALCULATE ( FIRSTNONBLANK('Tickets'[Ticket Number],1),'Status'[StatusID]="Open",'Type'[TypeID]="Client Management",'Client'[ClientID]=ClientID)
 
Where ClientID is a lookup for the selected (using a slicer) client ID. 
v-zhangti
Community Support
Community Support

Hi, @ericsara 

 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mohammedadnant
Impactful Individual
Impactful Individual

Hi @ericsara 

 

Pls try this DAX

VAR _TicketCount = CALCULATE ( COUNTROWS(Tickets), Status[Name]="Open", Type[Name]="Management")
RETURN
IF ( _TicketCount > 1, "N/A", 1)

 

If this is the answer to your query, please hit the like button. 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Hi Mohammed,

This does well to identify if there is only one ticket, but how do I return the Ticket Number?

Cheers, 

Hi @ericsara 

 

Try this

VAR _TicketCount = CALCULATE ( COUNTROWS(Tickets), Status[Name]="Open", Type[Name]="Management")
RETURN
IF ( _TicketCount > 1, "N/A", MAX('Table'[Ticket Number]))

 

or just this below as a new measure

IF( HASONEVALUE('Table'[Ticket Number])=TRUE(), MAX('Table'[Ticket Number]), "N/A")

 

 

If this is the answer to your query, please hit the like button. 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Hi Mohammed,

Not sure I am following 100%.  Are you saying to use your original solution with the new one or just the new one without the original?

I think you are saying to use just the new one.  If so, does it not assume that the Ticket Number is known?  How is the logic finding the ticket number for the client, that also has a Status of Open and a Type of Management?

 

Cheers, 

Hi @ericsara 

 

you are right, there are 2 options

1st solution enhancement is to get the ticket number instead of 1

2nd solution is that you will select those 2 options in a slicer or filter on this visual in filter pane, Status=Open, Type=Management.

 

Hope now it is clear.

 

If this is the answer to your query, please hit the like button. 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.