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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors