topic Re: Excel to filter some weeks in DAX Commands and Tips

Excel to filter some weeks

Anonymous — Mon, 15 Aug 2022 02:03:56 GMT

HI everyone.

I have a column called "upc" that are keys for different products. Also in my fact table i have the start day of the week of the transaction.

I did some calculations and i created a table object:

(in the table above im filtering by upcs, always i want to see only one upc)

I would like to be able to eliminate some rows (weeks) from differents upc.

For example: Upc= 220044 (one key product), i want to hide week 82 because in that week happened something particular than means that that number is not representative for some "upc" and needs to be hiden to have cleaner data and insights.

I want to do that in some excel manually and then connect this excel to my pbi. Something like this:

I dont know how to proced.

Re: Excel to filter some weeks

PurpleGate — Mon, 15 Aug 2022 13:33:32 GMT

I have a messy solution.

I connected my excel to power bi

Added a custom column that joined the UPC and the Week number to both the Excel table and to the table with all the UPC in. this makes a unique key that I could use to merge the tables later.

On the excel table I also added a column called "remove" and just had the word "remove" in all of the rows

I then merged the excel onto my table using the "link" column I had just added

and removed all the rows that now had the word "remove" in them

and I removed the "link" column

Re: Excel to filter some weeks

Anonymous — Mon, 15 Aug 2022 13:56:39 GMT

Thank you for responding.

Im going to try your solution 😄

Re: Excel to filter some weeks

v-zhangti — Wed, 17 Aug 2022 07:17:09 GMT

Hi, Anonymous

You can try the following methods.

Sample data:

Table1:

Table2:

Measure:

Measure = VAR N1 = CALCULATE ( MAX ( 'Table 2'[weeks not to consider] ), FILTER ( ALL ( 'Table 2' ), [UPC] = SELECTEDVALUE ( 'Table 1'[UPC] ) && [weeks not to consider] = SELECTEDVALUE ( 'Table 1'[week] ) ) ) RETURN IF ( SELECTEDVALUE ( 'Table 1'[week] ) <> N1, 1, 0 )

Your selected week does not appear in the view. Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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