Forum Discussion
Getting a table from a measures and slicer values
Hi New to power bi, need help on this.
I've a table like this.
Every month a class is given codes which they have to complete the plan. if plan is equal to actual its named as Completed.
| Class | Code | Plan | Actual | Date |
| A | 100 | 1 | 1 | 1/1/2019 |
| A | 100 | 1 | 1 | 2/1/2019 |
| A | 100 | 1 | 1 | 3/1/2019 |
| A | 100 | 1 | 1 | 4/1/2019 |
| A | 100 | 1 | 0 | 5/1/2019 |
| A | 101 | 1 | 1 | 1/1/2019 |
| A | 101 | 1 | 1 | 2/1/2019 |
| A | 101 | 1 | 1 | 3/1/2019 |
| A | 101 | 1 | 1 | 4/1/2019 |
| A | 101 | 1 | 1 | 5/1/2019 |
| B | 102 | 1 | 1 | 1/1/2019 |
| B | 102 | 1 | 1 | 2/1/2019 |
| B | 102 | 1 | 1 | 3/1/2019 |
| B | 102 | 1 | 1 | 4/1/2019 |
| B | 102 | 1 | 1 | 5/1/2019 |
| B | 103 | 1 | 1 | 1/1/2019 |
| B | 103 | 1 | 1 | 2/1/2019 |
| B | 103 | 1 | 1 | 3/1/2019 |
| B | 103 | 1 | 1 | 4/1/2019 |
| B | 103 | 1 | 1 | 5/1/2019 |
What I want is for a selected month/months need to get a table like this
when selected until 4/1/2019, resulting table should show like below
| Class | Completed Codes |
| A | 2 |
| B | 2 |
But when selected until 5/1/2019, resulting table should show like below
| Class | Completed Codes |
| A | 1 |
| B | 2 |
Because Class A Code 100 on 5/1/2019 is not completed.
Please help me on this
Thanks.
Hi azeem_3a ,
Based on what you have presented I can duplicate your requested output. I have added a column in Power Query, although you can add it in Power BI. Simple if statement comparing the Plan and Actual and if not equal give it a 1. Call it Not completed. My table is call code. Then a measure.
Distinct count of projects = DISTINCTCOUNT(code[Code])
and another
Completed projects = [Distinct count of projects] - max(code[Not completed])
works on a card or table.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
7 Replies
- Nathaniel_C
Community Champion
azeem_3a ,
Go to power query, and create a conditional column as in pic, then add your slicer with the dates, add a table, with Class, and then add the conditional column, using the arrow, select sum. See pic. Make sure your new col is of type whole number.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHi Nathaniel_C
Thanks for the reply, but my issue is a bit different one.
I have obtained my table through summarized function hence can not edit through query.
Also my requirement is when i select all the dates until 1st April(Jan,Feb, Mar,Apr), it should show like this. (Since both classes have completed all the codes in the given month period)
Class Completed Codes A 2 B 2 When I select all the dates until 1st May it should show like this. (Since Code 100 in Class A for the month of May they havent completed)
Class Completed Codes A 1 B 2 Requirement is to check, for the selected periods whether the codes required to complete are complete or not.
Please assist me on this.
Thanks
- Nathaniel_C
Hi azeem_3a ,
Please give us more information, First, is this a classroom assignment or a business assignment? Second, what software are you worrking with? Does not my picture match your expected results?
Thank you,
Nathaniel
