Forum Discussion
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
- Client is selected via a Slicer
- Status = Open
- 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,
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.
7 Replies
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
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
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.
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.
