Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The problem comes with this source data imported from a folder -- multiple tables appended together
Item No. | Date | Buying Price | Selling Price | Margin |
1 | 4/20/2020 | 4 | 5 | 1 |
1 | 4/16/2020 | 4 | 5 | 1 |
2 | 4/16/2020 | 4 | 5 | 1 |
The problem here is I created a matrix to show the margin over time like this:
Item No\Margin\Date | 4/16/2020 | 4/20/2020 |
1 | 1 | 1 |
2 | 1 |
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.
Solved! Go to Solution.
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.
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.
Hi, thanks for your reply. I might not explain clearly before.
Here are two original tables:
1 - week 16, with 8 items
2- week 17: with 7 items, item 8 was discontinued in week 17.
As I imported these data from a folder, it was appended to one sheet like this:
Based on the imported data, I created a matrix to compare the margin over time:
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.
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.
You want to delete it, delete it, or just not show it?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |