Need Help Combining multiple Power BI formulas

[emembreno81]

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:

MI Exclude from Report Data (Y/N) =

var _value =

if('rep v_asrt_mo_rep'[BOM CC #]=BLANK() && 'rep v_asrt_mo_rep'[Legacy Style #] =BLANK() && 'rep v_asrt_mo_rep'[Legacy CC #]=BLANK() && 'rep v_asrt_mo_rep'[Primary Ticket Price]<0.01 && 'rep v_asrt_mo_rep'[Color Short Description] = "" || 'rep v_asrt_mo_rep'[Color Short Description] = "tbd" , "Y","N")

return

_value

I don't know if any of you have any suggestsions on how to make this formula work.  I would greatly appreciate any help!

[emembreno81 · Accepted solution]

ah, ok - I was able to figure out something else that seemed to do the trick!  

[ToddChitt]

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?

[emembreno81]

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?

[ToddChitt]

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?

[emembreno81]

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