Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Check based on Group of items

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...
Need Help
v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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?