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

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'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,