Forum Discussion
Check based on Group of items
Thanks for the quick reply, see below.
EXAMPLE
| Item Number | Date | Family OOS Status | Literal question / answer |
| 5 | 29-Mar-20 | N | Was an item in Family 2 with Velocity Code 1 in stock? Items 4 does not appear in OOS detail on that date, therefore was in stock and was not orered, flag should be reflected as 'N'. |
| 8 | 25-Mar-20 | Y | Was an item in Family 2 with Velocity Code 1 in stock? Item 8 is a Velocity Code 1, and Items 8,9,14 does not appear in OOS detail on that date, therefore was in stock and was orered, flag should be reflected as 'Y'. |
Sales Detail
| Item Number | Date | Family OOS Status |
| 5 | 29-Mar-20 | N |
| 8 | 25-Mar-20 | Y |
| 8 | 26-Mar-20 | Y |
| 8 | 29-Mar-20 | Y |
| 9 | 25-Mar-20 | Y |
| 9 | 26-Mar-20 | Y |
| 9 | 29-Mar-20 | Y |
| 10 | 26-Mar-20 | N |
| 10 | 28-Mar-20 | N |
| 11 | 25-Mar-20 | N |
| 11 | 26-Mar-20 | N |
| 11 | 29-Mar-20 | N |
| 14 | 25-Mar-20 | Y |
| 14 | 26-Mar-20 | Y |
| 14 | 29-Mar-20 | Y |
OOS Detail
| Item Number | OOS Date |
| 2 | 25-Mar-20 |
| 6 | 25-Mar-20 |
| 10 | 25-Mar-20 |
| 10 | 29-Mar-20 |
| 12 | 26-Mar-20 |
| 12 | 28-Mar-20 |
| 12 | 29-Mar-20 |
| 13 | 25-Mar-20 |
| 13 | 26-Mar-20 |
| 13 | 28-Mar-20 |
| 13 | 29-Mar-20 |
| 15 | 25-Mar-20 |
| 15 | 26-Mar-20 |
| 15 | 28-Mar-20 |
| 15 | 29-Mar-20 |
Item Master
| Item Number | Family | Velocity Code |
| 2 | 1 | 99 |
| 3 | 1 | 3 |
| 4 | 1 | 1 |
| 5 | 1 | 2 |
| 6 | 1 | 99 |
| 7 | 1 | 2 |
| 8 | 2 | 1 |
| 9 | 2 | 1 |
| 10 | 2 | 2 |
| 11 | 2 | 2 |
| 12 | 2 | 2 |
| 13 | 2 | 1 |
| 14 | 2 | 1 |
| 15 | 2 | 2 |
Community SupportHi,
According to your description, please try this measure:
Measure =
VAR family =
CALCULATETABLE (
DISTINCT ( 'Item Master'[Family] ),
FILTER (
ALLSELECTED ( 'Item Master' ),
'Item Master'[Item Number] IN DISTINCT ( 'Sales Detail'[Item Number] )
)
)
VAR items =
CALCULATETABLE (
DISTINCT ( 'Item Master'[Item Number] ),
FILTER (
ALLSELECTED ( 'Item Master' ),
'Item Master'[Family] IN family
&& 'Item Master'[Velocity Code] = 1
)
)
RETURN
IF (
CALCULATE (
COUNTROWS ( 'OOS Detail' ),
FILTER (
ALLSELECTED ( 'OOS Detail' ),
'OOS Detail'[Item Number] IN items
&& 'OOS Detail'[OOS Date] IN DISTINCT ( 'Sales Detail'[Date] )
)
) > 0,
"Y",
"N"
)And i think according to the logic you explained, my test result is a little different from your expected result, please check it.
My test result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
I think your work is definately getting my closer to my goal, I'll just have to confirm the data and tweak where necessary.
Adding this as a calculated column seems to work great, but when I tried to add another column I'm now getting this error:
Neither of these columns are referencing each other, but can be looking at some of the same tables/columns to derive their results. Any thoughts on this one?
I think I see where part of the unexpected results could be coming from. In my production data, there could be many items with Velocity Code '01' and if any of those Velocity Code '01' items are available in the same family (not in the OOS Detail table) then I would consier that Family to have it's Velocity Code '01' on stock and should be producing an 'N' at the end result.
- v-gizhi-msft
Hi,
According to your further explaination, i am still confused about your expected result on Item10 and 11.
Could you please explain more about why Item10 and 11 expected result are N and Item 14 is Y?
This will let me to help you further.
Expect your reply!
Thanks!
Best Regards,
Giotto