Flagging entire category if one item is flagged

[mafaber]

Hi,

I have two tables, one dim table indicating if an item is shortage item or not, and a fact table containing all orders (items).

The slight twist is that some items must be delivered together thus if even one item within a delivery group is shortage item, I want to see all the other item in that delivery group flagged.

So I have an item column with the list of items, I have a shortage flag per item in a related dim table and a delivery group category column. If a single row is flagged, all the rows with the same delivery group number should be flagged (0 or 1 for example).

I appreciate the help! 

[v-juanli-msft · Accepted solution]

Hi @mafaber 

If i understand you correctly, you could create a calculated column

new flag = var sums=CALCULATE(SUM(dim[flag]),FILTER(dim,dim[group]=EARLIER(dim[group]))) return IF(sums>=1,1,0)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mafaber , you have to use all expect

these two will help

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390