Forum Discussion
Problems modelling Excelfile for use in PBI
OK, here is how I would go about it. It is a different method to yours though...
1) Unpivot the data on import: select the period and date columns and under unpivot choose "unpivot other columns"
2) Add a conditional column to create a new column classifying categories as right or wrong:
and
Your Fact table should now look like this (I´ve actually deleted the period column you had, since I always include it in my calendar table):
3) Create a Calendar Table
4) I've also created a Category Table by referencing from the fact table, deleting all columns except the Category and Right or Wrong Columns, and deleted duplicate values. The table looks like this:
5) Load all into the model and create the relevant relationships between tables. The model should resemble this:
6) Create any measures you need for the pie chart; I've just made a simple sum of values.
7) Create your pie chart using the "Right or Wrong" and "Category" fields from your Category Table as your legend and your measure as values:
You can now drill down/up etc on your pie chart (by right-clicking on a segment or using the drill-triggers on the chart):
and once you have "drilled-down":
And here is the PBIX file if you're interested:
Pie Chart ExampleHope it helps, and let us know if something isn't working for you!
OK, here is how I would go about it. It is a different method to yours though...
1) Unpivot the data on import: select the period and date columns and under unpivot choose "unpivot other columns"
2) Add a conditional column to create a new column classifying categories as right or wrong:
and
Your Fact table should now look like this (I´ve actually deleted the period column you had, since I always include it in my calendar table):
3) Create a Calendar Table
4) I've also created a Category Table by referencing from the fact table, deleting all columns except the Category and Right or Wrong Columns, and deleted duplicate values. The table looks like this:
5) Load all into the model and create the relevant relationships between tables. The model should resemble this:
6) Create any measures you need for the pie chart; I've just made a simple sum of values.
7) Create your pie chart using the "Right or Wrong" and "Category" fields from your Category Table as your legend and your measure as values:
You can now drill down/up etc on your pie chart (by right-clicking on a segment or using the drill-triggers on the chart):
and once you have "drilled-down":
And here is the PBIX file if you're interested:
Pie Chart Example
Hope it helps, and let us know if something isn't working for you!
Thank you for the feedback. It makes sense to unpivot and that I had to add the dimension right / wrong. From there on I will follow your instructions.
I get so frustrated by DAX and seem to lock up. Modeling an ordinary DB is no problems but I lack experience in modeling for BI. Those two facts makes this an excellent learning experience.
PaulDBrownThank you. I had a good time solve all the little puzzles. I ran in to problems Copying table and Distinct it. I got an error message about the Date-columns I deleted so I created that table in Excel instead. Besides that I had no real problems. I Find it less frustrating working with Power Queary than with DAX. 😤
I'm glad you worked it out. Indeed, DAX is challenging, and the learning never stops!
It is good that you feel comfortable in Power Query and it is better to get as much done as possible before loading the data to the model (either at the data source or in Power Query) and start working on the DAX.
I recommed you spend some time looking at videos and reading a couple of books.
Also something which is often overlooked is the importance of setting up the model and the structure of the tables (data or fact tables and lookup or Dim tables).
And of course, come back here whenever you get stuck!
One detail question about the Table Main Measures you created using DAX(?) or by right clicking. When I inspect yor table the header is blank. When I create a table I get Value1 rammed in my throat. I did an ugly wourk around. Your Table and code window is yellow mine is blue.