Home Forums Power Pivot DAX – Countrows BETWEEN values

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 8 months, 1 week ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #9549

    hansenius
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

     

    Hi,

    I struggle to figure out a solution here, and I have tried googling and attempting all variants, but I keep getting error message. This code I have attached is the closest I have come to a solution. Basically, if an order has more than 10 lines AND less than 16, then it should count the order. I have no problems with the code for orders which are between 1 and 5 lines, as no “between” is needed.

    Code:

    PPS11-15:=countrows(
    Filter(
    values(‘Tabell__192 168 2 2_Effect_V_Excel_PLL'[OrdreNr])
    ; calculate(countrows(‘Tabell__192 168 2 2_Effect_V_Excel_PLL’) ) >10 || calculate(countrows(‘Tabell__192 168 2 2_Effect_V_Excel_PLL’) ) <16
    )
    )

    The problem is that this just gives me a number which is way off, which probably means that the code gives me AND, but not BETWEEN.

    I hope somebody can help me out, since DAX can make me stuck for days. It is foreign territory for me.

    Kinds regards,
    Eirik

    #9550

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Eirik,

    The double-pipe (||) symbol means OR, so you are probably getting a count of all orders in the filter context.

    Replace the double-pipe with the double-ampersand (&&) and you will have your AND that creates the “between” you are looking for.

    Tom

    #9553

    hansenius
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Thank you for clarifying this. I wasn’t aware of the difference, but I had in my efforts attempted &&. It generates an error message: Formula contains the invalid sign ]

    It is confusing, because if I replace && with ||, then I get no error message.

    Code:

    PPS11-15:=countrows(
    Filter(
    values(‘Tabell__192 168 2 2_Effect_V_Excel_PLL'[OrdreNr])
    ; calculate(countrows(‘Tabell__192 168 2 2_Effect_V_Excel_PLL’) ) >10 && calculate(countrows(‘Tabell__192 168 2 2_Effect_V_Excel_PLL’) ) <16
    )
    )

    I was able to bypass this problem by basically creating formulas that only used one filter “>”, and then subracting through seperate formulas. Basically, the amount of orders with order lines 6-10 equals the amount of orders with order lines less then 11, minus orders with order lines less than 6. It works, but it is a gaffa-tape solution. I would still like to understand the use of BETWEEN in the example above.

    Eirik

    #9554

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    If you could attach some sample data, I would also test. You should be able to use either the && operator or the AND function to create a “between” effect (there is not a BETWEEN function).

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.