Find a single value in a table based on multiple criteria

[ericsara]

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 Number	Ticket Name	Client ID	Status ID	Type ID
AU-4589	Management - Tables R US	1000	OP	MAN
AU-4599	Seup up account	1000	CL	TAS
NZ-5100	Management - Trucks n Trucks	1001	OP	MAN
NZ-5645	Help find keys	1001	OP	SUP
NZ-6545	Management - One Sock	1002	CL	MAN

Client Table

Client ID	Name	Address
1000	Tables R US	1 Your Place
1001	Trucks n Trucks	22 Me Drive
1002	One Sock	4 Lost Place

Status Table

Status ID	Name	Description
OP	Open	Ticket is active
CL	Closed	Ticket is closed

Type Table

Type ID	Name	Description
MAN	Management	To manage the overall status of your client
TAS	Task	An item to out on your to do list
SUP	Support	Support 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 Selected	Ticket Number shown in card
Tables R US	AU-4589
Trucks n Trucks	NZ-5100
One Sock	N/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, 

[ericsara · Accepted solution]

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]

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]

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

[ericsara]

Hi Mohammed,

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

Cheers, 

[mohammedadnant]

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

[ericsara]

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, 

[mohammedadnant]

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