Excel to filter some weeks

[Anonymous]

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.

[v-zhangti · Accepted solution]

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.

[PurpleGate]

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

[Anonymous]

Thank you for responding.

Im going to try your solution 😄