cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soulhunting
Frequent Visitor

How to use DAX/Power Query to perform calculation with multiple if else condition

Hi, I have the following table in Power BI, how do I generate the last column 'Average_Awareness_Percentage' in Power Query/DAX. I have described the intended formula for column 'Average_Awareness_Percentage'. Not sure if this can be achieved in Power Query but I have no experience using DAX either.

 

Any help from the community to get me started with this would be helpful!

 

Staff ID DepartmentYearStandardsQuestionNoQuestionResponse_NoResponseAverage_Awareness_Percentage
203382IT2020IFRS151Are you aware of the IFRS15 standards?1_AYes1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' )
205282Accountant2020IFRS151Are you aware of the IFRS15 standards?1_BNo1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' )
205928Accountant2020IFRS151Are you aware of the IFRS15 standards?1_AYes1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' )
112959Accountant2020IFRS152Which of the following is an exception for application of IFRS 15?2_CInsurance(Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
112966Accountant2020IFRS152Which of the following is an exception for application of IFRS 15?2_AMusic Subscription (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
123459Accountant2020IFRS152Which of the following is an exception for application of IFRS 15?2_BMobile Service (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
205262Accountant2020IFRS152Which of the following is an exception for application of IFRS 15?2_DLease(Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
205982IT2020IFRS152Which of the following is an exception for application of IFRS 15?2_EAll of above(Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
239282Accountant2020IFRS152Which of the following is an exception for application of IFRS 15?2_EAll of above(Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2'
177959Accountant2020IFRS163Are you aware of the IFRS16 standards?3_ANoTotal Count Response is '3_B'/Total Count of Department where QuestionNo is '3'
192902IT2020IFRS163Are you aware of the IFRS16 standards?3_ANoTotal Count Response is '3_B'/Total Count of Department where QuestionNo is '3'
205208Accountant2020IFRS163Are you aware of the IFRS16 standards?3_BYesTotal Count Response is '3_B'/Total Count of Department where QuestionNo is '3'
352959Accountant2020IFRS163Are you aware of the IFRS16 standards?3_BYesTotal Count Response is '3_B'/Total Count of Department where QuestionNo is '3'
352959Accountant2020IFRS164Which of the following shall a lessee recognise at commencement?4_ARight-of-use(Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B'
263935Accountant2020IFRS164Which of the following shall a lessee recognise at commencement?4_BLease-liability(Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B'
653964Accountant2020IFRS164Which of the following shall a lessee recognise at commencement?4_EUncertain(Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B'
205282Accountant2020IFRS164Which of the following shall a lessee recognise at commencement?4_EUncertain(Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B'
1 ACCEPTED SOLUTION
Rocco_sprmnt21
Community Champion
Community Champion

a somewhat cumbersome solution. but it seems to be in the nature of the problem

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdRb9owEP4rJ55WrYjEJqF5mqDdpElrpUGnaWorZNyDWErtKHbK+u/nI4VRqRDUIUSqPcQ5J+fjvs/Od8fNTYsFnJ+x1mnr67UfWMACsr8MR2HkjdBf/QLhyZQg5sJbZgouRag8wDqh70Vxbz+R87jvx19oyYY2fLg2TmRwbkrtYIg2N9oiKAu3ZRCwXjgeVEZn3c/Hv8BcFO4B/Wzl3JeSXgvtqjnMU/TZfC/ROmX0lVmL++xx0ro7JXwRW+D7G+HfcQ78eGWODGbCzvYM8wi3MwxZEiV1OGm/f6ZKpkt8U5NlZq70jOIKDfhbYk6/5N8UIPI8U1Is5n4BhYEwIg7Y+JzialsWQkv0dg0LrP+c7keocVzSdQId2C9jrLqt+IrjA/JFZ+aytErCqJxYWahqWQOZY7x70JNGonJpJipDGGHxqCQ2kDWS27hWbvfI2oUfv6GwDfw2SbM3l949cvSZ9iPL6LmYmMcmUsWTHar4f8rW9avXq6mUVBb41o4gftkR8IW6Lxqf7RD5Dr3AZhR8HUXCkuDVb6QJ2ZMaBlu7sjegGKy6skPB4FFd0/WOYHQ3i4hNhZcEARlaiwgFSjPTyicrHEjz4FORSPkQwO7itA3VLHVtM22XuxQov2ZHDemuNKTzmuP4BQVL3+pIxjzh0cFIGCzLcztTwvc2yj0dBw9xxJO4ezAeqJ788E8KJ5Q+Dgbq/xm/awbu/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff ID " = _t, Department = _t, Year = _t, Standards = _t, QuestionNo = _t, Question = _t, Response_No = _t, Response = _t, Average_Awareness_Percentage = _t]),
    mt = Table.TransformColumnTypes(Origine,{{"Staff ID ", Int64.Type}, {"Department", type text}, {"Year", Int64.Type}, {"Standards", type text}, {"QuestionNo", Int64.Type}, {"Question", type text}, {"Response_No", type text}, {"Response", type text}, {"Average_Awareness_Percentage", type text}}),
    count = [1_B = List.Count(List.FindText(mt[Response_No],"1_B")),
            2_B = List.Count(List.FindText(mt[Response_No],"2_B")),
            3_B = List.Count(List.FindText(mt[Response_No],"3_B")),
            4_B = List.Count(List.FindText(mt[Response_No],"4_B")),
            2_A = List.Count(List.FindText(mt[Response_No],"2_A")),
            4_A = List.Count(List.FindText(mt[Response_No],"4_A"))
          ],
    acc = Table.AddColumn(mt, "AAP", each 
    if [QuestionNo]=1 then count[1_B]/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=1)) else
    if [QuestionNo]=2 then (count[2_B]+count[2_A])/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=2)) else
    if [QuestionNo]=3 then (count[3_B])/Table.RowCount(Table.SelectRows(mt, (r)=>  r[QuestionNo]=3)) else
    if [QuestionNo]=4 then (count[4_B]+count[4_A])/count[3_B]   
    else "other")
in
    acc

 

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

Hello @soulhunting 

 

why not create for every logic a measure? Where you then use the filter-function to filter your table and then make your calculation. This would be the most natural way to proceed.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Rocco_sprmnt21
Community Champion
Community Champion

a somewhat cumbersome solution. but it seems to be in the nature of the problem

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdRb9owEP4rJ55WrYjEJqF5mqDdpElrpUGnaWorZNyDWErtKHbK+u/nI4VRqRDUIUSqPcQ5J+fjvs/Od8fNTYsFnJ+x1mnr67UfWMACsr8MR2HkjdBf/QLhyZQg5sJbZgouRag8wDqh70Vxbz+R87jvx19oyYY2fLg2TmRwbkrtYIg2N9oiKAu3ZRCwXjgeVEZn3c/Hv8BcFO4B/Wzl3JeSXgvtqjnMU/TZfC/ROmX0lVmL++xx0ro7JXwRW+D7G+HfcQ78eGWODGbCzvYM8wi3MwxZEiV1OGm/f6ZKpkt8U5NlZq70jOIKDfhbYk6/5N8UIPI8U1Is5n4BhYEwIg7Y+JzialsWQkv0dg0LrP+c7keocVzSdQId2C9jrLqt+IrjA/JFZ+aytErCqJxYWahqWQOZY7x70JNGonJpJipDGGHxqCQ2kDWS27hWbvfI2oUfv6GwDfw2SbM3l949cvSZ9iPL6LmYmMcmUsWTHar4f8rW9avXq6mUVBb41o4gftkR8IW6Lxqf7RD5Dr3AZhR8HUXCkuDVb6QJ2ZMaBlu7sjegGKy6skPB4FFd0/WOYHQ3i4hNhZcEARlaiwgFSjPTyicrHEjz4FORSPkQwO7itA3VLHVtM22XuxQov2ZHDemuNKTzmuP4BQVL3+pIxjzh0cFIGCzLcztTwvc2yj0dBw9xxJO4ezAeqJ788E8KJ5Q+Dgbq/xm/awbu/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff ID " = _t, Department = _t, Year = _t, Standards = _t, QuestionNo = _t, Question = _t, Response_No = _t, Response = _t, Average_Awareness_Percentage = _t]),
    mt = Table.TransformColumnTypes(Origine,{{"Staff ID ", Int64.Type}, {"Department", type text}, {"Year", Int64.Type}, {"Standards", type text}, {"QuestionNo", Int64.Type}, {"Question", type text}, {"Response_No", type text}, {"Response", type text}, {"Average_Awareness_Percentage", type text}}),
    count = [1_B = List.Count(List.FindText(mt[Response_No],"1_B")),
            2_B = List.Count(List.FindText(mt[Response_No],"2_B")),
            3_B = List.Count(List.FindText(mt[Response_No],"3_B")),
            4_B = List.Count(List.FindText(mt[Response_No],"4_B")),
            2_A = List.Count(List.FindText(mt[Response_No],"2_A")),
            4_A = List.Count(List.FindText(mt[Response_No],"4_A"))
          ],
    acc = Table.AddColumn(mt, "AAP", each 
    if [QuestionNo]=1 then count[1_B]/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=1)) else
    if [QuestionNo]=2 then (count[2_B]+count[2_A])/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=2)) else
    if [QuestionNo]=3 then (count[3_B])/Table.RowCount(Table.SelectRows(mt, (r)=>  r[QuestionNo]=3)) else
    if [QuestionNo]=4 then (count[4_B]+count[4_A])/count[3_B]   
    else "other")
in
    acc

 

View solution in original post

edhans
Super User
Super User

This may require a combination of Power Query and DAX. DAX is best for analysis and averages, but it seems you have some logic embeded in the fields but I cannot figure it out. Just a sample of the questions.

 

For the record, I am a CPA in the US (we kind of told IFRS to go take a hike though given it has been removed from the CPA exam and after over a decade of debate, still isn't required) but I don't understand your data below well enough to begin to think of the code logic, much less actually write the code.

Be sure to explain the data clearly.  

edhans_0-1612811128115.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors