Forum Discussion

Matej's avatar
Matej
Helper I
2 years ago

Return orders under specific value

Hi guys, 

I have two defined measures [Revenue] and [Orders]. Is there a way I can filter the [Orders] measure to return only those orders that are below certain [Revenue]? I can do some basic CALCULATE stuff but it seems that there is a different approach needed if I want to filter measure with another measure? 

 

[Revenue] = 

CALCULATE([Amount (Dr/Cr)],
'Chart of Accounts'[Name] = "510000 Sales : Trading"
)

[Orders] = 
DISTINCTCOUNT('Transaction Lines'[Document Number]),
    'Transaction Lines'[Type] IN {"Credit Memo", "Invoice"}
)


Thanks for any tips. 
Need Help

7 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, Matej 

     

    [Orders] = 

    Calculate (DISTINCTCOUNT('Transaction Lines'[Document Number]),

        'Transaction Lines'[Type] IN {"Credit Memo", "Invoice"},[Revenue] <25000

    )

     

    Here I take 25000 for example you can take as you want 

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

    • Matej's avatar
      Matej
      Helper I
      2 years ago

      Hi, 

      Thanks for the reply. This was my initial thought as well but unfortunatelly it does not work. When added to the table I get the following error message: "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

      Any ideas of how to go around this? 

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        Hi, Matej 

         

        These error come when we compare measure with condition without filter expression.

         

        Try below 

        [Orders] = 

        Calculate (DISTINCTCOUNT('Transaction Lines'[Document Number]),

         'Transaction Lines'[Type] IN {"Credit Memo", "Invoice"}, filter ('your table name',[Revenue] <25000))