Table listing Dimension even if no fact rows exist. Is this possible?

[mirzay]

I will try my best to explain, I have 2 dimensions and one fact.
DimCourses (only has 10 rows, each course has a different passing percent),
DimStudents,
FactGrades   (stores grades by student by course.

Modeled as such:

DimStudents ---->  FactGrades <--- DimCourses

DimStudents:

Name:

Jack

John

Mary

Trevor

....

DimCourses  (with passing percent for each):

Course            Pass:

Math               80

English            66

Physics            90

PE                    40

FactGrades:

Name    Course      Score
John      Math         85

John      English      75

John     Physics       85

John     PE               90

Mary     Math         98

Mary      English      97

Mary     PE               20

I have a slicer on DimStudents.Name, when I select Jack, table report should display:

Math              

English           

Physics           

PE                    

When I select Mary, it should display:

 Math           90              

English         97   

Physics           

PE                20

However, what is happening is if no fact exists, the courses column disappears also. I would like that to be static.

[Anonymous]

Hi @mirzay

You can check out the work around as below.

1. Create a measure  ScoresValue = sum(FactGrades[Score])+0

    By creating the measure in this manner ensures all the values of the Dimension gets a value, even if not found in Fact table.

2. Use this measure to display in the chart alongwith Course.

. 

4. Note that this will display 0 against Physics instead of Blank for Mary

5. If you want the value for Physics to be blank, create a measure 

   Scroestext = IF([ScoresValue]=0, " ", Format([ScoresValue],"##"))

   The output will look like

    

[mirzay]

Thank you @Anonymous, almost there. This works as requested and I like the 0 display also. However, there is another twist...

in Table, when I include the Pass% from DimCourses next to the column of my new measure as below...

Course,  Pass%,     Score       

If a student does not exist in Fact, The Courses are retained and score shows as 0. This is good. 

But my static pass percent per course also switches to 0.  I get this from the same DimCourse dimension as I get the courses from.

I used the concept above also and created a measure for pass% but it is still zeroing out.

Only one colmn from DimCourses is retaining value.

[Anonymous · Accepted solution]

Hi @mirzay

I went about doing this as follows

1. Create a column in FactGrades table

    PassFail = If ([Score]>Related(DimCourses[Pass]),1,0)

    Against each row of the table it will compare Score against the corresponding Pass mark from course table , if it is greater it wilk set it as 1 otherwise 0.

2. I then built a table using the command

   AverageMarks = Summarize(FactGrades,FactGrades[Course],"Avg",average(FactGrades[Score]),
                                                  "passed",Calculate(count(FactGrades[PassFail]),FactGrades[PassFail]=1),
                                                     "studentsAppeared",Calculate(countrows(FactGrades)) )

The above expression creates a table called AverageMarks by summarizing the FactGrades table by FactGrades[Course] and

compute the average of the scores by course, number of students passed by course using the PassFail column introduced and

the students appeared by course .

3.  Created a pass% column in the table AverageMarks as

     Pass% = divide([passed],[studentsAppeared],0)

4. Created a report using the columns from AverageMarks table and the output is as under

    

Hope this satisfies your need.  This output cannot be taken by Name as it will not convey anything meanigful.

If it works please accept this as solution and also give Kudos.

CheenusSing