Forum Discussion

Maverick423's avatar
Maverick423
Regular Visitor
6 years ago
Solved

Retrieve just one quote

Hello PBI Experts,

In my report, I have a table that should contain selected fields from Opportunity and Quotes. I need to retrieve just one or the first quote linked to an entity considering that Opportunity to Quotes has 1->M relationship. How do I filter the Quotes so that my table would only contain one QUOTE linked to an Opportunity?

 

Regards,

Maverick423

 

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Maverick423 

     

    You can create a Measure like below, and use it later as Filters in your Table visual.

    Measure = 
VAR _firstQuoteID = { CALCULATE( MAX( Quotes[qtID] ), ALL( Quotes[qtID] ) ) }
RETURN 
CALCULATE(
    COUNTROWS( Quotes ), 
    KEEPFILTERS( TREATAS( _firstQuoteID, Quotes[qtID] ) ) 
)

    The attached file contains an applied solution.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski


     

     

     

6 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Maverick423 

     

    Can You create a sample of your data.

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Maverick423's avatar
      Maverick423
      Regular Visitor
      6 years ago

      Hello Mariusz,

      Please see below for my test data in a table:

       

      As you can see, I have here two Quotes under 1 Opportunity. The requirement is just to diplay the first Quote if there are 2 or more Quotes linked to an Opportunity. Is it possible to do it using DAX?

       

      Regards,

      Maverick423

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Maverick423 

         

        You can create a Measure like below, and use it later as Filters in your Table visual.

        Measure = 
VAR _firstQuoteID = { CALCULATE( MAX( Quotes[qtID] ), ALL( Quotes[qtID] ) ) }
RETURN 
CALCULATE(
    COUNTROWS( Quotes ), 
    KEEPFILTERS( TREATAS( _firstQuoteID, Quotes[qtID] ) ) 
)

        The attached file contains an applied solution.

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski