If formula using both and or dax forumla

[Anonymous]

I'm working on a report where I need to evaluate the number of hours a resource is committed to a project. I'm trying to identify if the resource has been committed, not committed, or not assigned. My data set looks like this:

Project	Resource Role	June Hours Needed	June Hours Committed	July Hours Needed	July Hours Committed
Project A	Project Manager	160	160	100	100
Project A	BSA	100	0	50	0
Project A	Software Developer	50		50

The report requires me to count the number of resources who have been committed, declined (hrs committed = 0), or not assigned (hours committed = 'blank')

So far I got:

Committed = if([june hours committed]>0 || (july hours committed)>0,"Y")

Declined = if (isblank([Committed]) && (june hours committed)=0 || (july hours committed) = 0,"N")

Not Reviewed = if(isblank(Committed) && isblank(Declined), "blank")

My results are coming out  as follows:

Project	Resource Role	Committed	Declined	Not Reviewed
Project A	Project Manager	Y
Project A	BSA	Y	N
Project A	Software Developer			blank

I can't seem to find the error in my logic and could use some help. If there is a better way to address this, I'd appreciate the suggestions as well, 

I Dutt

[v-easonf-msft]

Hi , @Anonymous 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
More people will learn new things here. If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi， @Anonymous 

It's so strange..

I get a different result than you using your formula .

PBIX attached

Can you  share you expected output ? It will help to provide the appropriate solution.

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

Good morning!

So the expected output should show if the hours committed are provided (x > 0), if the hours committed were declined (x = 0), or if the hours committed were not addressed (x='blank'). Here is the example:

Project	Resource Role	June Demand Hrs	June Committed Hrs	July Demand Hrs	July Committed Hrs	Hours Committed	Hours Declined	Hours Not Reviewed
Project A	Project Manager	160	160	100	100	Y
Project A	BSA	50	0	50	0		Y
Project A	Software Developer	50		50				Y

I hope this helps, 

Neel D

[mahoneypat]

To do this, I would transform your data as follows (put this M code into a blank query to see the steps (paste over the existing text in Advanced Editor)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNKBs30T8xLTU4uAIoZmBgjSAEbG6qDqcwp2hMuDsCmEga4sOD+tpDyxKFXBJbUsNSe/AGwDWK0CghEbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, #"June Hours Needed" = _t, #"June Hours Committed" = _t, #"July Hours Needed" = _t, #"July Hours Committed" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"June Hours Needed", Int64.Type}, {"June Hours Committed", Int64.Type}, {"July Hours Needed", Int64.Type}, {"July Hours Committed", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,99999,Replacer.ReplaceValue,{"June Hours Committed", "July Hours Committed"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Project", "Resource Role"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Month"}, {"Attribute.2", "Category"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns",99999,null,Replacer.ReplaceValue,{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Category]), "Category", "Value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if [Hours Committed] = [Hours Needed] then "Committed" else if [Hours Committed] = 0 then "Declined" else "Not Assigned"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}})
in
#"Changed Type2"

Then simply make a matrix visual with Project and Resource as Rows, the new Custom column as Columns, and the count of Column in the values area.  The visual should look like this.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat