Forum Discussion
Adding a new column for a Matrix Table
Dear all,
This is my first post here, hope to get a solution for my what I'm facing 🙂
I have a matrix table and I'm trying to achieve a specific result to be showed, adding a new column
Basically, customer want to see if in a specific month, a forwarder that was awarder within a lane did not carry any shipment , not even one.
Awarded is already filtered to show only awarded, column Accepted shows if there where instances where something was accepted or rejected (0 not accepted, 1 is accepted)
I want to add a column that says something like:
"If carrier was awarded and they did at least one shipment , even though in the same month they did not accepted something then is OK, else NotOK" and then I can filter out what I don't need.
In the example attached, if we take into account july 2021, we see that we have in accepted column one line for 0 and one line for 1, means that forwarder did at least one shipment so needs to be excluded
I've tried for an entire day to find a solution but I could not get where I want
I'm attaching a screenshot of my current matrix to help
Thanks in advance
Claudio
Hi Faracla,
You can try to use the following dax formulas to show the result based on conditions:
Measure version:
formula = VAR rCount = CALCULATE ( COUNTROWS ( Table ), FILTER ( ALLSELECTED ( Table ), [AwardedYN] = 1 && [Accepted YN] = 1 ), VALUES ( Table[LaneCode] ), VALUES ( Table[MonthYear] ) ) RETURN IF ( rCount > 0, "OK", "NotOk" )Calculate column version:
formula = VAR rCount = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[LaneCode] = EARLIER ( Table[LaneCode] ) && Table[LaneCode] = EARLIER ( Table[MonthYear] ) && Table[AwardedYN] = 1 && Table[Accepted YN] = 1 ) ) RETURN IF ( rCount > 0, "OK", "NotOk" )Regards,
Xiaoxin Sheng
3 Replies
Hi Faracla,
You can try to use the following dax formulas to show the result based on conditions:
Measure version:
formula = VAR rCount = CALCULATE ( COUNTROWS ( Table ), FILTER ( ALLSELECTED ( Table ), [AwardedYN] = 1 && [Accepted YN] = 1 ), VALUES ( Table[LaneCode] ), VALUES ( Table[MonthYear] ) ) RETURN IF ( rCount > 0, "OK", "NotOk" )Calculate column version:
formula = VAR rCount = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[LaneCode] = EARLIER ( Table[LaneCode] ) && Table[LaneCode] = EARLIER ( Table[MonthYear] ) && Table[AwardedYN] = 1 && Table[Accepted YN] = 1 ) ) RETURN IF ( rCount > 0, "OK", "NotOk" )Regards,
Xiaoxin Sheng
Hi Anonymous ,
first of all, thanks for your answer , i tried your suggestions but unfortunately id did not work as expected, please see screen attached:
As you can see, the final result is "Not OK" for all of them, while the expected result should be that for carrier "TEST 1" result should be "OK" because in the month of august, even if they have some 0 in colum "AcceptedYN", they also have 1 , means they still did carry some shipments, therefore are to be excluded.
Carrier "TEST" is correct because in the month of august all value in colum "AcceptedYN" is 0 and this is what will be flagged to customer.
Attaching a new screenshot of expected result, columns in read are the result of your suggestions, column in Yellow is the expected resultHi Anonymous
there was a bug in the code ,I substitute and it worked perfectly!
here the one it workedformula = VAR rCount = CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[LaneCode] = EARLIER ( Table[LaneCode] ) && Table[MonthYear] = EARLIER ( Table[MonthYear] ) && Table[AwardedYN] = 1 && Table[Accepted YN] = 1 ) ) RETURN IF ( rCount > 0, "OK", "NotOk" )
thank you very much for your help 😄
