Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check if row exist in former week and remove rows based on result

The problem comes with this source data imported from a folder -- multiple tables appended together 

 

Item No.DateBuying PriceSelling PriceMargin
14/20/2020451
14/16/2020451
24/16/2020451

 

The problem here is I created a matrix to show the margin over time like this:

Item No\Margin\Date4/16/20204/20/2020
111
21 

 

In this case, the margin for item 2 would be blank.

I'm wondering if there is a way that I can search for items that did not exist for the latest data table and delete its record in the former data? For instance, I want to delete the item 2 if it didn't exist in the latest table.

Thanks for any help.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I modified data to reproduce your scenario.

    Table:

     

    You may create a measure as follows.

    Visual Control = 
var itemno = SELECTEDVALUE('Table'[Item No])
var _maxdate =
CALCULATE(
    MAX('Table'[Date]),
    ALLSELECTED('Table')
)
var latestweek = 
CALCULATETABLE(
    DISTINCT('Table'[SourceName]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Date] = _maxdate
    )
)
var margin =
CALCULATE(
    SUM('Table'[Margin]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Item No] = itemno&&
        'Table'[Date] = _maxdate&&
        'Table'[SourceName] in latestweek
    )
)
return
IF(
    NOT( ISBLANK(margin) ),
    1,
    0
)

     

    Finally you may put it in the visual level filter to display the result.

     

    Best Regards

    Allan

     

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

     

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your reply. Yes, actually I just want to hide it rather than delete the record. 

      I am looking for a way to compare the current week's item number and former week's item number to mark those items existed before but not now. 

      based on the marked column (1 or 0), I can apply that as a filter to the visual.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

     

    IsDisplay = 
var itemno = SELECTEDVALUE('Table'[Item No.])
var latestdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
         ALLSELECTED('Table'),
         'Table'[Item No.] = itemno
    )
)
return
IF(
    latestdate = TODAY(),
    1,
    0
)

     

     

    Then you need to put the measure in the visual level filter. Today is 4/21/2020. Here is the result.

     

    Best Regards

    Allan

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, thanks for your reply. I might not explain clearly before. 

      Here are two original tables:

      1 - week 16, with 8 items

      week 16

       2- week 17: with 7 items, item 8 was discontinued in week 17. 

      week 17

      As I imported these data from a folder, it was appended to one sheet like this:

      PowerBI Import

      Based on the imported data, I created a matrix to compare the margin over time:

      PowerBI Matrix

      what I see here, item 8's margin in week 17 is blank because it's discontinued. In this case, I don't want to see item 8 at all, since it's already been discontinued, I really don't care about its past performance. So my question here is, how can I mark item 8 and filter it out?

      I'm trying to find a way to compare item numbers and filter out those discontinued items.

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi, Anonymous 

         

        Based on your description, I modified data to reproduce your scenario.

        Table:

         

        You may create a measure as follows.

        Visual Control = 
var itemno = SELECTEDVALUE('Table'[Item No])
var _maxdate =
CALCULATE(
    MAX('Table'[Date]),
    ALLSELECTED('Table')
)
var latestweek = 
CALCULATETABLE(
    DISTINCT('Table'[SourceName]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Date] = _maxdate
    )
)
var margin =
CALCULATE(
    SUM('Table'[Margin]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Item No] = itemno&&
        'Table'[Date] = _maxdate&&
        'Table'[SourceName] in latestweek
    )
)
return
IF(
    NOT( ISBLANK(margin) ),
    1,
    0
)

         

        Finally you may put it in the visual level filter to display the result.

         

        Best Regards

        Allan

         

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