- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 | Department | Year | Standards | QuestionNo | Question | Response_No | Response | Average_Awareness_Percentage |
203382 | IT | 2020 | IFRS15 | 1 | Are you aware of the IFRS15 standards? | 1_A | Yes | 1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' ) |
205282 | Accountant | 2020 | IFRS15 | 1 | Are you aware of the IFRS15 standards? | 1_B | No | 1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' ) |
205928 | Accountant | 2020 | IFRS15 | 1 | Are you aware of the IFRS15 standards? | 1_A | Yes | 1 - (Total Count Response is '1_B'/Total Count of Department is 'Accountant' where QuestionNo is '1' ) |
112959 | Accountant | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_C | Insurance | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
112966 | Accountant | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_A | Music Subscription | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
123459 | Accountant | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_B | Mobile Service | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
205262 | Accountant | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_D | Lease | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
205982 | IT | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_E | All of above | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
239282 | Accountant | 2020 | IFRS15 | 2 | Which of the following is an exception for application of IFRS 15? | 2_E | All of above | (Total Count Response is '2A' + Total Count Response is '2_B') / Total Count of Department is 'Accountant' where QuestionNo is '2' |
177959 | Accountant | 2020 | IFRS16 | 3 | Are you aware of the IFRS16 standards? | 3_A | No | Total Count Response is '3_B'/Total Count of Department where QuestionNo is '3' |
192902 | IT | 2020 | IFRS16 | 3 | Are you aware of the IFRS16 standards? | 3_A | No | Total Count Response is '3_B'/Total Count of Department where QuestionNo is '3' |
205208 | Accountant | 2020 | IFRS16 | 3 | Are you aware of the IFRS16 standards? | 3_B | Yes | Total Count Response is '3_B'/Total Count of Department where QuestionNo is '3' |
352959 | Accountant | 2020 | IFRS16 | 3 | Are you aware of the IFRS16 standards? | 3_B | Yes | Total Count Response is '3_B'/Total Count of Department where QuestionNo is '3' |
352959 | Accountant | 2020 | IFRS16 | 4 | Which of the following shall a lessee recognise at commencement? | 4_A | Right-of-use | (Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B' |
263935 | Accountant | 2020 | IFRS16 | 4 | Which of the following shall a lessee recognise at commencement? | 4_B | Lease-liability | (Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B' |
653964 | Accountant | 2020 | IFRS16 | 4 | Which of the following shall a lessee recognise at commencement? | 4_E | Uncertain | (Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B' |
205282 | Accountant | 2020 | IFRS16 | 4 | Which of the following shall a lessee recognise at commencement? | 4_E | Uncertain | (Total Count Response is '4_A' + Total Count Response is '4_B')/Total Count Response_No is '3_B' |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Anonymous
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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 ReportingHelpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
06-18-2024 07:07 AM | |||
05-13-2024 03:04 AM | |||
11-02-2023 12:06 PM | |||
04-30-2024 12:55 PM | |||
11-29-2024 03:07 AM |
User | Count |
---|---|
17 | |
14 | |
13 | |
9 | |
8 |