Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 create...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

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.