Return orders under specific value

[Matej]

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. 

[Dangar332]

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]

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]

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))

[Matej]

Hi, 

Thanks for trying to help me. I have used the amended DAX but it seems like the last condition with [Revenue] < 25 (it is 25 in my case) is just ignored. The new measure returns same amount for each client in matrix as:

CALCULATE(

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

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

)

[Anonymous]

Hi @Matej 

You cna try tge following measure

[Orders] =
CALCULATE (
    DISTINCTCOUNT ( 'Transaction Lines'[Document Number] ),
    FILTER (
        'Transaction Lines',
        'Transaction Lines'[Type]
            IN { "Credit Memo", "Invoice" }
                && [Revenue] < 25
    )
)

Best Regards!

Yolo Zhu

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

[Matej]

Hi, 

Thanks fo the reply. Unfortunatelly, the last part of the measure ([Revenue] < 25) is being ignored. The measure returns the same values as without that part and I know for sure that there is a number of orders that has smaller revenue. 
The only thing that worked is IF([Revenue] < 25,1,0). This measure correctly marks small revenue orders with 0 but I can't get it to sum total number of those measures. 

[Dangar332]

hi, @Matej 

can you provide some data for seeing what's problem ?