cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
evalromf
Helper I
Helper I

AND IF in conditional column

Hi,

 

I'm trying to create a new column that should be populated with a certain number based on the contents of several other columns.

 

What I'm looking for is something like this:

 

If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals Alltel, then "1"

Else If [COUNTRY] equals Kazakhstan AND IF [COMPANY] equals VIP, then "2"

 

But i cannot find such a possibility in "Add conditional column". (see picture) 

 

AddAdditonalColumn.JPG

 

 

 

 

 

 

And when I try to use the Advanced Editor, I cannot find any syntax that covers this scenario.

 

Does anyone have a solution for this?

 

 

53 REPLIES 53
Anonymous
Not applicable

Hi @GilbertQ BeforeBeforeOn using new measureOn using new measure

 

The new measure is not working fine as the report contains columns from some other tables like T1, T2.

 

Can you suggest on this 

 

 

Hi there

If you can post some sample data, with the measures we can then assist?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ 

 

Its difficult to post data as it is coming from two or three tables and they contain many columns. But can you explain why i am not able to put a simple condition.

 

Example if am i am doing (billing = "FP") it should not give at row level but at the aggregated level without being affected by other columns in the report.

 

How can i create a measure so that it gives billing = FP without getting affected by any dimension??

 

 

Hi there

I would suggest first to simplify your model design if you are trying to achieve this from 2-3 different tables. Ideally all that information would be contained in 1 table.

The challenge is that when creating a measure it does not work on a column basis but rather works on returning a single scalar value. With the IF condition it still expects to get a single value once evaluated?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@GilbertQ  Below is the error i am getting. Now i am getting the data from 2 tables project, staff and calendar table is also used. Can you now suggest on this.

 

ErrorErrorProjectProjectSchemaSchema

Hi there

It is because the IF statement only accepts a single value to compare. You need to change the context to be using a measure value and not a column value.







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Can you please explain how can i implement the below logic now:

 

if(billingmethod= "FP" then use Measure1 else use Measure2)

 

 

You could try it with a Variable such as below

My Measure =
VAR ValueToGet = SELECTEDVALUE(TableName[BillingMethod])

RETURN
IF(ValueToGet = "FP", [Total_Invoice].[Total_InvoiceTM])




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@GilbertQ 

 

Thank you so much, it worked. I am new to learning power bi and dax. Can you please any book or online course where I can learn better and explore on myself.

Hi there

No worries glad to help out

My mate @MattAllington  has a great course here: https://exceleratorbi.com.au/courses-overview/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@GilbertQ 

@Michiel 

 

My Measure is giving correct result acorss column ie column subtotal is correct but row subtotal is wrong.

 

Can you suggest what could be wrong.Capture1.JPGCapture2.JPG

'Wrong totals' is usually an effect of misunderstanding what the total row contains: it is not the total of the rows above, it's the result of the same measure in a different context, in this case without the row identifiers filtered.
So your measure is not a simple SUM, but you do want the total to add up. The solution in this case is to force DAX to calculate results on a row-by-row basis, you can do this with SUMX. Trying to mimic your model:

SUMX(VALUES(ProjectTable[Complete Project Detail]), [Your original measure])

or, if Complete Project Detail is unique in the Project table:

SUMX(Project, [Your original measure])

 

If you don' want to call another measure [Your original measure] but want to include the logic in this measure directly, it's probably safest to wrap it in CALCULATE:

SUMX(
    VALUES(ProjectTable[Complete Project Detail]),
    CALCULATE(<Your original formula goes here>)
)

Anonymous
Not applicable

Capture.JPG

My measure is having the above calculation. The main measure is " Pay Distribution Project" . Can you please suggest now ,where I am going wrong.

 

Anonymous
Not applicable

Hi all,

 

 

I have 2 measures (M1, M2) which are working fine in two separate scenarios. But now i need to combine these two scenarios.


I have to put a simple condition in a final measure (M3) using column "Billing"

 

M3 = If ( Billing = "FP", M1,M2)

 

But I am unable to use column name in the measure. Please help.

Hi there

Should it not be the following:

M 3 = IF(VALUES(TableName[Billing]) = "FP",[M1],[M2])




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

 
 
 
 

Before I was getting proper OutputBefore I was getting proper OutputNew  measure is not working fineNew measure is not working fine

 

Hi @GilbertQ 

 

I tried this but when i am using it in report its not working fine as the report contains the columns from other tables  like T1, T2

RodrigoTXRA
Helper I
Helper I

Hi All

 

Just to give you more feedback about my data, this is what I have:

 

1 - An excel table which contains a column (Request (Project & Ad Hoc) where one of these 4 letters can be found, and each letter represents a number as follows

F = 70

P = 80
G = 100
E = 116

2 - So I require to transform these letters into their respective values so I can use it in further calculations.

 

 Capture.PNG

Hi there

Will it have the individual number in the column?

If not if you could post some sample data of what the column currently looks like




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ please see screen shot of the excel table below. The column in black is the one I have issues with. This column comes with a letter instead of those numbers. Let me know if you require more info
Example.PNG

Hi there

Thanks for the screenshot.

 

What you could do, is go into the Power Query Editor, click on Add Conditional Column from the Add Column Ribbon.

 

Then you can use the conditional logic in which you can put in your requirements as shown below.

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.