Power BI Dax questions

[Anonymous]

I am building a report card like dashboard that only needs to show all 31 courses (whether completed or not), the weighted score(score * weight of course), and attempts taken to complete course. Sample data is as follows :

Course Name	Score	Points	Attempts	Name	Weighted Score (score * point weight)
Lesson 1	80	10	1	Jim Bob	8
Lesson 2	90	10	2	Tex Mex	9
Lesson 3	100	10	1	Jacki Chan	10
Lesson 4	87	10	2	John Doe	8.7
Lesson 5	70	10	1	Onna Fairway	7
Lesson 6	67	10	1	Geek Guy	6.7
Lesson 7	98	10	1	Skeeg	9.8
Lesson 8	40	10	1	Danny Downs	4
Lesson 9	78	10	1	Tex Mex	7.8
Lesson 10	90	10	1	Bad Worker	9
Lesson 11	90	10	1	Good Worker	9
Lesson 12	80	10	2	Onna Fairway	8
Lesson 13	90	10	3	Jim Bob	9
Lesson 14	80	10	1	Jim Bob	8
Lesson 15	70	10	5	John Doe	7
Lesson 16	89	10	2	Jacki Chan	8.9
Lesson 17	80	10	5	Tex Mex	8
Lesson 18	78	10	4	Danny Downs	7.8
Lesson 19	87	10	1	Good Worker	8.7
Lesson 20	98	10	1	Good Worker	9.8
Lesson 21	89	10	1	Chris Mendez	8.9
Lesson 22	77	10	2	Sherry Lien	7.7
Lesson 23	88	10	2	Onna Fairway	8.8
Lesson 24	99	10	2	Jim Bob	9.9
Lesson 25	60	10	2	Skeeg	6
Lesson 26	58	10	2	Skeeg	5.8
Lesson 27	0	100	1	Tex Mex	0
Lesson 28	0	50	1	Onna Fairway	0
Lesson 29	0	100	1	Geek Guy	0
Lesson 30	0	100	1	Geek Guy	0
Lesson 31	0	100	1	Tex Mes	0

First issue:

I cannot get all 31 courses to show and be a static table. When using the "employee" as a slicer the table adjusts to only the completed courses. How do I join a table to my "report card data" so that it will be static and only adjust per employee slicer. 

Second issue:

I am having trouble with the attempts affecting the weighted score. In the image below you can see that the weighted score should not be over "10". But if a course was attempted 3 times then the weighted score is tripled. How can we fix this to have these two fields not correlate. 

3rd issue:

I have used the "enter data" to create a table of just the list of 31 courses. In order to get the 31 courses to stay how can I build the same measures in this "course list table" or can I just use the ones from my "report card data".

[Anonymous · Accepted solution]

Hi @Anonymous ,

For your first question, you need to have the same attribute columns between the two tables in order to make the association, that is to say, the report card data and the table of the sample data need to have a column that meets the similar characteristics at the same time in order to establish the relationship, and after the relationship is established, apply the RELATED function to make the connection and then you can add the Slicer.

For your second question, it looks like you only need to use the Score and Point columns in the table. So you can create a Calculated Column.

Column = 'Table'[Score]/'Table'[Points]

For your third question, you can certainly apply the same MEASUREMENT in a newly created form, and you can make your own attempts.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.