How would I consolidate these columns after appending queries? (Conditional Column question)

[GooseHelpful199]

I have 2 queries that have Item, Description, and Values that I appended together in order to get them on the same visuals. The issue I have is that since the descriptions for these items are different in each query, I need to have a column that only has 1 Description per Item #. 

Would it be possible to create a Conditional Column like the one highlighted below? I want it to use Description1 first and foremost, but if there is no result then to use Description2 instead. The goal is to only have one possible Description for each Item# in that Conditional Column.

[Ashish_Mathur]

Hi,

You can avoid the creation of a conditional column if you ensure that the heading of the Description column is exactly the same in both tables.

[rsbin]

@GooseHelpful199 ,

Please try this:

NewDescription = IF( ISBLANK( [Description1] ), [Description2], [Description1] )

This will result in Row3 and Row4 to be R and B, not Red and Blue.  I trust this is what you are expecting, not what you have shown in your original post.

Regards,

[GooseHelpful199]

Sorry I probably could have worded by question better, but Row3 and Row4 of my screenshot are correct. What I meant was that if there is no Description1 of that item# anywhere in the appended sheet, to use Description2 instead.

Like I said, for each Item# I only want one possible Description result. This way if I create a table visual with Item, Number, Sales, and Shipments, I will only get 1 Description. If I do it with your formula, I will get one row with Description1 and Sales, and another with Description2 and Shipments.

[rsbin · Accepted solution]

@GooseHelpful199 ,

My Bad on the original.  Was too hasty to provide a solution that I didn't read carefully enough.

I hope this time works:

NewDescription = 
VAR _Item = [Item#]
VAR _Description =CALCULATE( MAX('Description'[Description1]), FILTER( 'Description', 'Description'[Item#] = _Item ))
                     
RETURN
  IF( _Description <> BLANK(), _Description, [Description2] )

Source	Item#	Description1	Description2	Sales	Shipments	NewDescription
Query1	1	Red		5		Red
Query1	2	Blue		5		Blue
Query2	1		R		5	Red
Query2	2		B		5	Blue
Query2	3		G		5	G

It works for this small sample, I can only hope that it works for your larger dataset.

Kind Regards,

[GooseHelpful199]

First of all, thank you so much! This did exactly what I was looking for, even with a much larger dataset.

Do you mind walking through a bit what is going on in your formula? Would love to understand how you came about this solution.

[rsbin]

@GooseHelpful199 ,

First off, so very glad this worked for you!

NewDescription = 
VAR _Item = [Item#]
VAR _Description =CALCULATE( MAX('Description'[Description1]), FILTER( 'Description', 'Description'[Item#] = _Item ))
                     
RETURN
  IF( _Description <> BLANK(), _Description, [Description2] )

 Will try to explain:

1) Establishes or declares the current filter context.  i.e sets the variable to Item 1

2) Am capturing the Description1 value for the item declared above.  So for item 1 the Value is "RED" .  So now everytime Item 1 is found in the row, the New Description is "RED"

3) Is the test whether a value was found.  If  Not Blank(), then choose the value, else use the value in Description2.

A Google search on something like DAX using Variables should give you loads of examples and probably much better explanations.

Hope this helps some.

Best Regards,