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 CAL...
Need Help
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))

 

 

 

Matej's avatar
Matej
Helper I
2 years ago

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"}
)
  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Matej 

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Matej
      Helper I
      2 years ago

      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.