Filtering according to month and category

[Andvil]

Hello,

I have a database and need to show all the sales codes with a specific restriction: Sales codes with prices under $400 and weight under 4kg.

In a previous post, I received help with this formula and it helped: price = calculate(sum(Table[price]),allexcept(Table[Order Code])) and then i had to filter it to be lower than $400.

The problem is that, now I want to analyze by month and when I select a month in my slicer, my visual keeps the same and is not being filtered.

Thank you for the help,

JALV

[HotChilli]

If you add the 'Dates Table' into the ALLEXCEPT clause of each measure, it will work.

ALLEXCEPT removes all filters except the the ones stated, so adding the table in will allow you to filter using the slicer.

Technically, I'm pretty sure you can just add the single column (month) but adding the table means you can use any column in the date table to filter.

I recommend changing the field in the slicer to MonthNameLong rather than use the built-in Date->Mes because you have an excellent Date table with a lot of flexibility so you might as well use it.  Use 'SortByColumn' feature to get the correct order.

[Andvil]

Hi @HotChilli,

That is what I have been trying all day 😞

I did this but it still does not work when I filter: 

price = calculate(sum(Table[price]),allexcept(Table[Order Code]), allexcept('Dates Table'[MonthNameLong]))

Actually I want to use, the "month name long" column as you mention.

Can you please help me with this?

Best,

JALV

[HotChilli]

Too many allexcepts!

You're nearly there. Allexcept has the table name, then the filters that you want applied so just take your original measures in the supplied pbix and add the 'Dates Table'[MonthNameLong]  

And for the slicer, add the long month name to the slicer values, then remove the Date->Mes and use 'Sort By Order' feature to sort by month number

[Andvil]

Hi @HotChilli,

My slicer already has the "Month Name Long" and not the Date>Month.

I tried this: price = calculate(sum(Table[price]),allexcept(Table[Order Code], ('Dates Table'[MonthNameLong])) 

but it is still not working and i receive the error message "All arguments within an ALLEXCEPT function must be related to (or contained by) the table which is used as the first argument. Where a one-to-many relationship exists, the table which is used as the first argument must be on the 'many' side of that relationship"

I do not have much experience with DAX so I actually do not know how to use the calculate and all except formulas.

Best,

JALV

[HotChilli]

It may seem harsh but you won't learn anything if I write it.

Read my previous post.

What you just tried is not the original measure in the pbix with the date column added.

[Andvil]

Hi @HotChilli,

I understand your point. I have been watching videos and browsing on internet to know more about this but still havent found a way to solve it.

My original formula would be:

price = CALCULATE(SUM(DAU_COURIER_2020[US$ FOB]),ALLEXCEPT(DAU_COURIER_2020,DAU_COURIER_2020[NUMERO DE CARGA FINAL],'DATES TABLE','DATES TABLE'[MonthNameLong]))

I have used all this variations:

I do not know what else I can try. I have seen on internet people using allexcept many times but is not working for me. I have removed the allexcep also.

Any advice regarding this please?

Best,

JALV

[HotChilli]

I feel like you have confused yourself and been guessing.

"If you add the 'Dates Table' into the ALLEXCEPT clause of each measure, it will work."

[HotChilli · Accepted solution]

price = CALCULATE(SUM(DAU_COURIER_2020[US$FOB]),
ALLEXCEPT(DAU_COURIER_2020,DAU_COURIER_2020[NUMERO DE CARGA FINAL],'DATES TABLE'))

[Andvil]

Hello @HotChilli,

Thank you very much!!!

I really appreciate your willingness to help and make me learn. Thank you for helping me, I was very confused and I have been a lot of time trying to figure out the answer. 

I tried your suggestion and it worked, thanks a lot!!!!!!!

I am currently taking a course in DAX, hope it helps me to understand better this language and figure out whatever I need.

So far, let me thank you again for your help!

Best,

JALV