Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
@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.
Hi , @awitt
Sorry for later reply.
According to your description , maybe we need to use measure rather than calculated column.
Here is a demo.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |