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)
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?
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??
@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.
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.
'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:
VALUES(ProjectTable[Complete Project Detail]),
CALCULATE(<Your original formula goes here>)
My measure is having the above calculation. The main measure is " Pay Distribution Project" . Can you please suggest now ,where I am going wrong.
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.
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
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.
@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
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.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.