Check based on Group of items

Question

Hi everyone,&nbsp;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.&nbsp;&nbsp;Any help with this would be great. Relevant Table information below:&nbsp;New column Required: FAMILY OOS STATUS, which would return a simply Y/N flag whether the conditions listed above are met.&nbsp;&nbsp;Sales DetailColumns: Item Number, Date,&nbsp;FAMILY OOS STATUS&nbsp;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&nbsp;Item MasterColumns: Item Number, Family Code, Velocity Code ('01', '02', '03', or '99')

v-gizhi-msft · Answer

Hi,&nbsp;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
                &amp;&amp; 'Item Master'[Velocity Code] = 1
        )
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'OOS Detail' ),
            FILTER (
                ALLSELECTED ( 'OOS Detail' ),
                'OOS Detail'[Item Number] IN items
                    &amp;&amp; 'OOS Detail'[OOS Date] IN DISTINCT ( 'Sales Detail'[Date] )
            )
        ) &gt; 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&nbsp;Hope this helps.&nbsp;Best Regards,Giotto

greg_deckler · Answer

Sample data would be great.&nbsp;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 · Answer

Thanks for the quick reply, see below.&nbsp;&nbsp;EXAMPLEItem NumberDateFamily OOS StatusLiteral question / answer529-Mar-20NWas 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'.&nbsp;825-Mar-20YWas 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'.&nbsp;&nbsp;&nbsp;Sales DetailItem NumberDateFamily OOS Status529-Mar-20N825-Mar-20Y826-Mar-20Y829-Mar-20Y925-Mar-20Y926-Mar-20Y929-Mar-20Y1026-Mar-20N1028-Mar-20N1125-Mar-20N1126-Mar-20N1129-Mar-20N1425-Mar-20Y1426-Mar-20Y1429-Mar-20Y&nbsp;OOS DetailItem NumberOOS Date225-Mar-20625-Mar-201025-Mar-201029-Mar-201226-Mar-201228-Mar-201229-Mar-201325-Mar-201326-Mar-201328-Mar-201329-Mar-201525-Mar-201526-Mar-201528-Mar-201529-Mar-20&nbsp;&nbsp;Item MasterItem NumberFamilyVelocity Code21993134115126199712821921102211221222132114211522

anonymous · Answer

I think your work is definately getting my closer to my goal, I'll just have to confirm the data and tweak where necessary.&nbsp;Adding this as a calculated column seems to work great, but when I tried to add another column I'm now getting this error:&nbsp;&nbsp;&nbsp;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?

anonymous · Answer

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.&nbsp;

Forum Discussion

Check based on Group of items

6 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Check column for similar values

Dynamic RLS based on sub group

FAQ on the Fabric User Groups

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'.

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 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