Forum Discussion
Need Help Combining multiple Power BI formulas
hello!
I'm working on a report that is based off of a direct connect to an existing Power BI dataset. I'm building new columns of data so that I can filter the information down and provide some indications to the team on what product still requires work to be done. The formulas that I am trying to combine are all working to confirm whether a task as been done or not. Unforunately, since I am doing a direct connect to a Power BI datasource, the new columns of information i've built then cannot be referenced in any new formulas, so I'm having to find creative ways to combine formulas so that I can filter out rows of data that don't require any work. The formulas I'm combining are:
Missing BOM CC# = IF('rep v_asrt_mo_rep'[BOM CC #]=BLANK(), "INCOMPLETE", "COMPLETE")
Missing Style #(6 digits) = IF('rep v_asrt_mo_rep'[Legacy Style #] =BLANK(), "INCOMPLETE", "COMPLETE")
Missing CC # (9 digits) = IF('rep v_asrt_mo_rep'[Legacy CC #]=BLANK(), "INCOMPLETE", "COMPLETE")
Invalid Ticket Price = IF('rep v_asrt_mo_rep'[Primary Ticket Price]<0.01, "INCOMPLETE", "COMPLETE")
Invalid Color =
var _value =
if('rep v_asrt_mo_rep'[Color Short Description] = "" || 'rep v_asrt_mo_rep'[Color Short Description] = "tbd" , "INCOMPLETE","COMPLETE")
return
_value
I've tried combining on my end, but ended up getting an error message with this formula:
ah, ok - I was able to figure out something else that seemed to do the trick!
5 Replies
Sounds like you are working with calculated COLUMNS, not MEASURES, correct? Are all these all in one table? What is the value of using the var _value / return _value structure? If each calculated column works OK, can you not reference it/them in the complex IF statement?
Hello Todd - Yes, I am working with calculated columns and not measures. These are all in one table. I am only using the var_value/return_value structure because it was what was in the original "Invalid Color" formula - I don't know if it's actually required in this new formula
The VAR / RETURN construct is handy if you need to calculate (read: Measures) something multiple times in a complex calculation. You can define it once, then reference it multiple times in the body, saves you from having to calculate it each time the formula comes across it :).
Does not really help you here.
So what happens if you build the IF statement one element at a time? Or build it off other calculated columns?
So that's part of the problem I keep running into - I want to build formulas off of the calculated columns that I've built, but they won't show up as one of the columns to work off of, so I keep having to find ways to combine the formulas from all of the calculated columns I've created. I don't know if I'm missing a step to link those columns back into the data?
ah, ok - I was able to figure out something else that seemed to do the trick!
