IF DAX

[BurakTurk]

Hi guys, 

I got a pretty complicated problem as seem on the pic below.

I want to make calculation for value formula and that must be about if column has yes, return it to 1, if no return it to 0 and if its none of them ( yes or no ) , just keep the numbers and sum all values in the value column .

I'd tired to do somethings but could not complete the formulas because of could not find any dax to keep values as before in the end of formula.

need your help dear master asap, thanks much))

Calculated Value Column = IF(File[Category]="A" && File[Value]="YES",1,IF(File[Category]="A" && File[Value]="NO",0, ...

[BurakTurk]

thanks all you guys, I overlooked something but just resolved them ;)) 

[BurakTurk]

guys hi,

firstly thanks much for all your helping, lets talk about the issue again,

There is a column which named as Category, and there are different two data which named as "A" and "B", 

another column which named as Value has both number and text ( they are the values of A and B ), texts and numbers are mixed in same column therefore its specified as Text.

The point is;

1-if there is any text value like yes and no which belongs to A and B category, return them to 1(yes) and 0(no), then leave the number values as they already are (I guess "leave numbers as they are" part of the formula should be at the end or somehow).

2-As last step, calculate all after these values in calculated value column recognized to Whole number . Hopefully I could clarify you clearly ((:

btw @tjdthank much to spend the attention, but I have to emphasize the A and B categories at each point in the formula, and gotta leave the values of a and b categories as if they have no text value, file [value] part unfortunately does not work on its own in the end.

[tjd · Accepted solution]

BurakTurk - Not sure what you're trying to do here, but here's a breakdown of the DAX I used: 

NewValue = IF(File[Value]="YES", "1", IF(File[Value]="NO", "0", File[Value]))

1. Create new calculated column called "NewValue"

2. If File[Value] = "YES" then insert a "1" into NewValue

3. If File[Value] = "NO", then insert a "0" into NewValue

4. If File[Value] is not equal to either YES or NO, then insert the value of File[Value] (that would be your original 2, 3, 4, ...) into NewValue.

The result is a column (NewValue) in the same table that has either a 1, a 0, or the original number from File[Value] in it.  Is that not what you wanted?  Not sure what you mean by "file [value] part, unfortunately, does not work on its own"; I tested it in PowerBI and it works.

[Greg_Deckler]

I have to agree with @tjd that this looks to be the solution to your issue @BurakTurk. The only thing I can think to add to @tjd's solution would perhaps be to do it this way to ensure a numeric number column:

NewValue = IF(File[Value]="YES", 1, IF(File[Value]="NO", 0, VALUE(File[Value])))

Now, once you have the NewValue column you can create a table or matrix based upon your category column. If you really want what you are saying, which is a new column with the total for each category I guess? You could do something like:

NewTotal = SUMX(FILTER(File,File[Category]=EARLIER(File[Category])),File[NewValue])

That should give you a new column where each A row has the total for all A's and where each B row has a total for all B's.

[Greg_Deckler]

So, what are you expected results from that example? I think I know, but it's not entirely clear.

[tjd]

@Greg_Deckler: You're one of my "Power BI heros" and you don't know the expected results?  It will be a numerical column of the desired values of the new calculated column "Value" by row "A", "B", ....  When put into a table or matrix it will show the sum of the A or B or ... columns.  Is that not obvious?

[Greg_Deckler]

I guess where I was getting hung up was: "just keep the numbers and sum all values in the value column ." And I was thinking, so he either wants 1 or 0 or the sum of all of the values in the column? But, re-reading it again, I think you got it correct and I overthought the issue.

[vijay_kolisetty]

Hi

If you are not able to do it in DAX, try to make this solution in the table/view level and import into it.

Apologies if I didnt provide feasible solution if you are not importing the data from table/view.

Regards

V

[tjd]

You could try:

NewValue = IF(File[Value]="YES", "1", IF(File[Value]="NO", "0", File[Value]))

File[Value] column needs to be type text.  After creating NewValue, convert the data type to whole number.

Hope this helps.