cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
texmexdragon
Power Participant
Power Participant

Mulitple Conditions (at row level)

Hello  -   I have a situation where we have Orders that have different codes.   Sometimes just one code.   Sometimes multiple.  

 

I would like to define a column that looks at each row, evaluating the RMA Order #, and see if there a header code (Hdr Prob Code).   If there is then this counts as one header code.  

 

If that RMA Order # repeats on another row....with a different Header Problem Code....then this RMA Order would be defined as having Multiple Header Codes.     If it only had one, it would be defined as Single.   

 

Single or Multiple Codes = IF(Flu_RMAs[RMA]=Flu_RMAs[Hdr Prob Code]="T1" && Flu_RMAs[Hdr Prob Code]="T2" || Flu_RMAs[Hdr Prob Code]="T3","Multiple","Single")
 
I tried something like this but it did not work. Any ideas are appreciated!
12 REPLIES 12
rickymazz
Resolver II
Resolver II

I don't know if I correctly understand your request but... 

 

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")
 
 
Change these with your variables:

Flu_RMAs = Your table name

RMA_Order = RMA Order

Hdr Prob Code = Header Code

amitchandak
Super User IV
Super User IV

Can you share sample data and sample output.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

 

A simplified example of the source data would be  (and the expected result in the last column)

 

RMA Order #      Hdr Prob Code              Single or Multiple    

 1234A                      T1                                    Multiple

 1234A                      T2                                    Multiple

 1234A                      T3                                    Multiple

 3458A                      T1                                    Single

 

So, in this example, because RMA Order 1234A is listed three times, each with a different Header code, this Order would be defined as "Multiple".   

 

Order 3458A only shows up once, with one Header code, and would be defined as Single.  

 

Does that help?

 

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")

Hi Ricky  -   Thanks for your help.... I get the red squiggly line error right at the very end....(see bolded text).    It says it is an "unexpected parameter" error.  

 

Single or Multiple Codes = IF(CALCULATE(COUNTROWS(Flu_RMAs), FILTER(Flu_RMAs, Flu_RMAs[RMA] = EARLIER(Flu_RMAs[RMA],FILTER(Flu_RMAs,Flu_RMAs[Hdr Prob Code] <> EARLIER(Flu_RMAs[Hdr Prob Code]))) > 1, "Multiple", "Single")

Check the Data type of your column:

Column Tools --> Data Type: Text

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 🙂 

 

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")

 

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 

 

 

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")
       

@rickymazz  -  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".   

 

single.png

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")

Create a new column like. Add condition to filter

Flag = if( countX(filter(table,table[RMA Order #]=earlier(table[RMA Order #])),table[RMA Order #])>1,"Multiple","Single")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors