Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a simple set of data 'TEST' as per below and I created an extra column on the status of the delivery.
The status is 1 when the tolerance is more than 3% above or below the ordered qty and 2 when within the tolerance.
'In Full Status' calculated column looks like this:
Now I look for a measure that needs to show that the sum of each status per order ID = zero when minimal 1 row = 1. And when its relevancy is not relevant its status should be not summed. And when all rows for the ID are a 2, then show a 1 as total result sum for the order ID
So as soon as one row of the order ID is a 1, then the sum of that order ID status should be zero. Only when all rows are 2, the sum for the order ID can show 1.
As result order ID 562 status will need to be summed to show zero
Order ID 563 status will be not relevant and not shown results for
Order ID 564 status will be summed to show as result 1
Had many attempts with keepfilters, calculate, and you name it but I can not seem to get it to work.
Thanks for any help
Table headers:
Relevancy
order ID
Product
Ordered qty
Delivered qty
Relevancy order ID Product Ordered qty Delivered qty
Relevant | 562 | A | 25 | 23 |
Relevant | 562 | B | 20 | 15 |
Relevant | 562 | C | 20 | 20 |
Not Relevant | 563 | X | 20 | 20 |
Not Relevant | 563 | C | 25 | 25 |
Relevant | 564 | X | 30 | 30 |
Relevant | 564 | C | 40 | 39 |
Relevant | 564 | B | 50 | 50 |
Relevant | 564 | A | 60 | 61 |
Solved! Go to Solution.
@DBNN
Please try the following:
Measure1 =
VAR _STATUS = VALUES(TEST[In Full Status] )
RETURN
IF(
MAX(TEST[Relevancy]) = "Relevant",
SWITCH(
TRUE(),
1 IN _STATUS , 0 ,
CALCULATE( COUNTROWS( _STATUS ) , TEST[In Full Status] = 2 ) = COUNTROWS( _STATUS ), 1 ,
BLANK()
),
BLANK()
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@DBNN
Please try the following:
Measure1 =
VAR _STATUS = VALUES(TEST[In Full Status] )
RETURN
IF(
MAX(TEST[Relevancy]) = "Relevant",
SWITCH(
TRUE(),
1 IN _STATUS , 0 ,
CALCULATE( COUNTROWS( _STATUS ) , TEST[In Full Status] = 2 ) = COUNTROWS( _STATUS ), 1 ,
BLANK()
),
BLANK()
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |