Home Forums Power Pivot DAX – Countrows BETWEEN values

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

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

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

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

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.