Check based on Group of items

[Anonymous]

Hi everyone, 

Another tricky problem here. I need to add a column in my Sales Detail table that will tell me whether or not the Family of that particular item had its '01' Velocity Code out of stock (specifically, when that item number appeared in the Out of Stock Detail table on the same date). There are many item numbers per Family, and each item may have different velocity codes and may not be out of stock at the same time. 

Any help with this would be great. Relevant Table information below:

New column Required: FAMILY OOS STATUS, which would return a simply Y/N flag whether the conditions listed above are met. 

Sales Detail

Columns: Item Number, Date, FAMILY OOS STATUS

Out of Stock Detail (item number is populated with the day it was out of stock, when it is in stock rows will not be added)

Columns: Item Number, OOS Date

Item Master

Columns: Item Number, Family Code, Velocity Code ('01', '02', '03', or '99')

[Greg_Deckler]

Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

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

[v-gizhi-msft]

Hi,

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:

pbix 

Hope this helps.

Best Regards,

Giotto

[Anonymous]

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

[Anonymous]

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?