Forum Discussion
Mulitple Conditions (at row level)
Hi Ricky - Just to clarify, I don't already have a column with these values in it (Single or Multiple). This is what I am trying to create.
So just to state my example again, I have a table that list all of our orders (RMA order). In this table, there is a field called Hdr Prob Code. This table is for Orders that have returns....issues....and the Prob Code is a code that defines what the issue is.
Some orders can have multiple problem codes. Some orders just have a single problem code.
I need to be able to distinguish which orders are "Multiple" or which orders are "Single" in terms of how many problem codes are associated with a particular order. Example:
Order # Prob Code Single or Multiple
1234A T2 Multiple
1234A T3 Multiple
3456A T2 Single
So, in this example, Order 1234A appears on two different rows....each with a different problem code....so it gets defined as having "Multiple" problem codes. Order 3456A just appears on one row, and just has one problem code....so it is defined as "Single".
I think that the formula who I showed you is correct for what you want to do, so you should try to solve the issue with that formula and you'll win 🙂
ps. Check if you have to use ";" delimiters instead of "," in the queries. It's strange that you receive an error, I think the DAX expression is correct
Anonymous - Hi Ricky - Here is how the results of your formula look. For example, RMA-004630 has two Problem Codes associated with it. It should be defined as an order with "Multiple" problem codes, but it is coming up as "Single".
Likewise, some Orders that only have one problem code are coming up as "Multiple".
Hi Ricky - There was not any issue with delimeters.
I am wondering if there is a way to make this approach work. It seems we can't do a combination of And/Or in the Switch statement, but something like that is what I think would work. Essentially the logic would be if an Order contains ONLY one of the below choices it would should return "Single" at the row level for each Order # that fits that criteria. If false, it has to be "Multiple".
Do you know of a way to make this work or something similar?
Multiple or Single = SWITCH(TRUE(),Flu_RMAs[Problem Code]="T1"||Flu_RMAs[Problem Code]="T2" &&Flu_RMAs[Problem Code]="T3" &&Flu_RMAs[Problem Code]="T4" &&Flu_RMAs[Problem Code]="T5" &&Flu_RMAs[Problem Code]="T6" &&Flu_RMAs[Problem Code]="T7","Single","Multiple")Hi, let's try the same formula but with ">=" instead of ">".
So:
Single or Multiple Codes = IF(CALCULATE(COUNTROWS(Flu_RMAs), FILTER(Flu_RMAs, Flu_RMAs[RMA Order #] = EARLIER(Flu_RMAs[RMA Order #])), FILTER(Flu_RMAs, Flu_RMAs[Hdr Prob Code] <> EARLIER(Flu_RMAs[Hdr Prob Code]))) >= 1, "Multiple", "Single")