Forum Discussion
Check based on Group of items
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 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