Forum Discussion
Add column values to filter without unpivot
Hi harshadrokade,
If this student data is your main fact table and not the student dimension, I'm not sure why you wouldn't unpivot the data. Power BI works better with narrow/tall fact tables.
In saying that, I obviously don't have the complete picture of your data model/requirements.
Can you provide an example of how you would want the slicer to work?
Are you wanting 'between' two values for any of the subject columns?
How is the 'Student Overall score' calculated?
If you could go down the unpivot path, with the assumptions I've made, I would have thought a data model more like the below would work better for you/be more performant.
Then you can achieve something like this...
See attached example PBIX.
So, I'm confident that unpivoting is the correct thing to do to the model for both performance and allowing for the simplest measure creation.
Did you review the PBIX file I attached?
It doesn't matter if the names were to duplicate in the detail table. Incidentally, if you have a student ID column, you wouldn't use the name in the detail table anyway.
When referencing the student name, it should be done with the column from the student dimension table which only has a single value for every student. Also, I added the overall score to the student dimension so it will not be duplicated.
Please review the PBIX file I'd attached previously and let me know if there's a reason that this model design won't work with your actual data.
Hi harshadrokade,
If this student data is your main fact table and not the student dimension, I'm not sure why you wouldn't unpivot the data. Power BI works better with narrow/tall fact tables.
In saying that, I obviously don't have the complete picture of your data model/requirements.
Can you provide an example of how you would want the slicer to work?
Are you wanting 'between' two values for any of the subject columns?
How is the 'Student Overall score' calculated?
If you could go down the unpivot path, with the assumptions I've made, I would have thought a data model more like the below would work better for you/be more performant.
Then you can achieve something like this...
See attached example PBIX.
Thanks KNP for your help.
I have a card visual where I want to see the respective subject score which name is selected into the slicer. I have two slicers as Student name & Subject. If I select Srudent A & Subject 1 in the slicers, I want to see 45. If I select Student C & Subject Subject 2, I want to see 9. The problem is how do I bring these Subjects names into one column so that I can use that column in slicer as below so that I can select them in slicer?
I dont want to unpivot Subject columns as it will then create duplicate values for Student names & Overall scores as multiple rows will be for these two columns data. The one I shared is the sample data from the table but there are many more columns in the table, & unpivot will make it very heavy as all these other columns values will get repeated in every row.
As I dont want to unpivot the data, even if I create a seperate table for Subjects, how do I connect the that table with main table ? Asking coz I have seperate columns for each subject? Are we saying we have to anyway unpivot data after which all Subjects will come in one column & then we can use the same in slicer as filter?
So, I'm confident that unpivoting is the correct thing to do to the model for both performance and allowing for the simplest measure creation.
Did you review the PBIX file I attached?
It doesn't matter if the names were to duplicate in the detail table. Incidentally, if you have a student ID column, you wouldn't use the name in the detail table anyway.
When referencing the student name, it should be done with the column from the student dimension table which only has a single value for every student. Also, I added the overall score to the student dimension so it will not be duplicated.
Please review the PBIX file I'd attached previously and let me know if there's a reason that this model design won't work with your actual data.
