Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
azeem_3a
Frequent Visitor

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 CodePlanActualDate
A100111/1/2019
A100112/1/2019
A100113/1/2019
A100114/1/2019
A100105/1/2019
A101111/1/2019
A101112/1/2019
A101113/1/2019
A101114/1/2019
A101115/1/2019
B102111/1/2019
B102112/1/2019
B102113/1/2019
B102114/1/2019
B102115/1/2019
B103111/1/2019
B103112/1/2019
B103113/1/2019
B103114/1/2019
B103115/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 

 

ClassCompleted Codes
A2
B2

 

But when selected until 5/1/2019, resulting table should show like below 

 

ClassCompleted Codes
A1
B2

 

Because Class A Code 100 on 5/1/2019 is not completed. 

 

Please help me on this 

Thanks. 

1 ACCEPTED SOLUTION

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. 

 

 

CProj.PNG

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Nathaniel_C
Super User
Super User

@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.
Nathaniel

 

 

code 1.PNG

 

Code 2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @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)

 

ClassCompleted Codes
A2
B2

 

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) 

 

ClassCompleted Codes
A1
B2

 

Requirement is to check, for the selected periods whether the codes required to complete are complete or not. 

 

Please assist me on this. 

 

Thanks 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C 

 

Im using Power Bi and I need a dax to solve the issue. 

 

In your picture, I get the answer I want when I click a particular month. 

When I click the months Jan,Feb,March,April(ALL at Once) the result is different. 

 

What I want is, if I select ALL months until April, it should give this result 

ClassCompleted Codes
A2
B2

 

When I click ALL Months until May, it should give me this result

 

ClassCompleted Codes
A1
B2

 

Hope you understand the question. 

 

Thanks 

Hi @azeem_3a ,

Sorry, I did miss that you were going to select multiple months.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @azeem_3a ,

So are these codes cumulative? What if April was the uncompleted code, and we went Jan - May, and May was completed would we get a zero or a one for that code?

 

Thanks,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

 

 

CProj.PNG

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.