Forum Discussion

ericsara's avatar
ericsara
Icon for Helper I rankHelper I
4 years ago
Solved

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 ...
Need Help
  • ericsara's avatar
    ericsara
    4 years ago

    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. 
ericsara's avatar
ericsara
Icon for Helper I rankHelper I
4 years ago

Hi Mohammed,

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

Cheers, 

mohammedadnant's avatar
mohammedadnant
Icon for Impactful Individual rankImpactful Individual
4 years ago

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's avatar
    ericsara
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      mohammedadnant
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      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