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, 

      • 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