How to build a flat table in a Report

[WishAskedSooner]

Hi Experts!

I am trying to find a way to present the underlying data to users in a Report as a flattened table. For example, if I have the following fact table:

Student	Class	Course	Grade
Akash	Sophomore	Math	A
Dave	Senior	Math	B
Akash	Sophomore	English	A
Dave	Senior	English	C
Akash	Sophomore	History	B
Dave	Senior	History	A

I want to pivot on the Course with Max Grade as values. Think how the PQ command works:

Table.Pivot( MyTable, List.Distinct( MyTable[Course] ), "Course", "Grade", List.Max )

Student	Class	Math	English	History
Akash	Sophomore	A	A	B
Dave	Senior	B	C	A

However, I can't figure out how to do this using PowerBI visuals because the Courses are dynamic as they are added and deleted all the time making Tables problematic. Plus, this is just an example. My actual fact table has several hundred columns.

Also, I don't want a collapsable hierarchy like in a matrix. I just want a flattened table like the above even with NULLS.

Then finally, I want to add a few slicers to the Report.

I looked at Paginated Reports, but this is not what I am after either.

Any thoughts? TIA!

[Sahir_Maharaj]

Hello @WishAskedSooner,

Can you please try this approach:

GradeValue = 
MAX( 'FactTable'[Grade] )

PivotedTable = 
SUMMARIZE(
    'FactTable',
    'FactTable'[Student],
    'FactTable'[Class],
    "Math", CALCULATE( MAX('FactTable'[Grade]), 'FactTable'[Course] = "Math" ),
    "English", CALCULATE( MAX('FactTable'[Grade]), 'FactTable'[Course] = "English" ),
    "History", CALCULATE( MAX('FactTable'[Grade]), 'FactTable'[Course] = "History" )
)

[WishAskedSooner]

@Sahir_Maharaj 

Thank you so much for you quick reply!

I tried your solution. I created a new table using your PivotedTable DAX statement. Then, I loaded all the columns of the table into a Table Visual. I was even able to add a slicer.

A couple of questions regarding your solution.

First, I don't see where the first measure, GradeValue, comes in.

Second, this requires me to hard code the Courses using DAX, yuck! My actual fact table has several hundred columns. If this really is the only way, then I would pivot my fact table using PQ, but I would then need to hand drag those several hundred columns into the Table Visual. I am hesitant to even try this because for all I know, it would break PBI.

Also, the Courses offered change from year to year. For example, if I have a slicer for year, and "Physics" is not offered in that year, I don't want it in my table.

I can almost build what I need with a matrix. The main problem is I don't want a collapsable hierarchy. Maybe there is a setting to turn it off? But, I can't find one.

Your thoughts?

[WishAskedSooner · Accepted solution]

I think I figured it out. I need to change the Layout for a Matrix to Tabular. That seems to work!