Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Excel to filter some weeks

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: 

Gustavo98_0-1660528424510.png

(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:

Gustavo98_1-1660528761147.png

I dont know how to proced.

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Sample data:

Table1:

vzhangti_0-1660718873112.png  

Table2:

vzhangti_1-1660718914942.png

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 )

vzhangti_2-1660720478941.png

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.

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Sample data:

Table1:

vzhangti_0-1660718873112.png  

Table2:

vzhangti_1-1660718914942.png

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 )

vzhangti_2-1660720478941.png

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
Resolver III
Resolver III

I have a messy solution. 

 

I connected my excel to power bi

PurpleGate_0-1660570042715.png

 

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

PurpleGate_1-1660570104241.png

 

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

PurpleGate_2-1660570233016.png

 

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

 

and I removed the "link" column

 

 

Anonymous
Not applicable

Thank you for responding.

 

Im going to try your solution 😄

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.