Multiple Field Filter Calculation

[awitt]

I have an orders table that consits of often times multiple items per order. What I am trying to accomplish is identifying when all the items in the order were "filled" in time and assigning a 1 or 0 to the entire order, not just the individual items. The data table is structured to where each record is an item or "OrderItemId" and there can be mulitple records for each "OrderID". What I need is to return a 0 if there is any instance of a 0 on the item fill column. 

For example for OrderID 12340036 only two items were filled in time so all values in the "Calculated" column should return 0.  The next step would be to add a filter that would exclude certain characteristics. For instance remove "Widget 2" from this logic entirely and only evaluate the other items when determining if the order was filled. 

PBIX file is attached. 

Sample PBIX File 

[v-easonf-msft]

Hi， @awitt 

If help ,change your measures  "calculated"as below:

Calculated =
VAR i = [OrderID]
RETURN
    IF (
        CALCULATE (
            COUNTROWS (
                FILTER (
                    ALL ( 'TempTable' ),
                    'TempTable'[OrderID] = i
                        && TempTable[OrderItem Fill Y/N] = 0
                )
            )
        ) > 0,
        0,
        1
    )

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

[awitt]

@v-easonf-msft Thanks! 

Do you have an idea how to answer the second part where you are filtering a certain subset of the data. The example I think was to make this calculation but not include the results for "widget 2" in the calculation. 

[v-easonf-msft · Accepted solution]

Hi , @awitt 

Sorry for later reply.

According to your description , maybe we need to use measure rather than calculated column.

Here is a demo.

pbix attached

If help ,try steps as below:

1.Create calculate table as a slicer

Table = DISTINCT('TempTable'[Product])

2.create measure as below" ,put it into the visual table

Measure = if(CALCULATE(COUNTROWS('TempTable'),FILTER(ALLSELECTED(TempTable),'TempTable'[OrderID] in DISTINCT('TempTable'[OrderID]) && 'TempTable'[OrderItem Fill Y/N] =0))>0,0,1)

Create measure  "visual control" ,put it into table visual filter pane as below:

visual control = 
 IF(DISTINCTCOUNT('Table'[Product]) <> CALCULATE(DISTINCTCOUNT('Table'[Product]),ALL('Table')),
IF(SELECTEDVALUE(TempTable[Product]) in DISTINCT('Table'[Product]),1,-1)
,-1)

3.create measure slicer visual control as below ,put it into slicer filter pane

Slicer Visual Control = IF(SELECTEDVALUE('Table'[Product]) in DISTINCT('TempTable'[Product]),1,-1)

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

[Greg_Deckler]

Sounds like you want On Time In Full. I thought I had posted that to the quick measure gallery. Apparently not, I know it is in my upcoming book. I will take a look at your PBIX and see if I can apply it.