Add column values to filter without unpivot

[harshadrokade]

Hi,

I have below table. I want to create a filter that will have values from column Subject1, Subject2 & Subject3. One option is unpivot column Subject1, Subject2 & Subject3 & then use that 'Attribute' column in slicer but I dont want to do that as other columns get repeated, increasing the size of the data.

How to add these three column headers in slicer & then filter their data without unpivot? Like creating a seperate table with these three column values, etc..

Student name	Student Overall score	Subject1	Subject2	Subject3
A	63	45	99	45
B	38	15	23	78
C	39	67	09	43

[KNP · Accepted solution]

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.

[Anuja_Chaudhari]

Unpivoting the columns Subject1, Subject2, and Subject3 into a single column allows you to create a slicer without the need for a separate table.

beacuse minimizing data duplication and reducing the size of the dataset.

By using unpivot, you can efficiently create a slicer with values from multiple columns without the need for a separate table. This approach keeps your dataset clean and reduces redundancy, ensuring optimal performance in Power BI. If the increase in data size is not a concern and you prefer simplicity, unpivot remains a preferred method.

[KNP · Accepted solution]

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.

[harshadrokade]

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?

[KNP · Accepted solution]

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.

[JorgePinho]

I think that using field parameters might help you.

Check this out and let me know what you think: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters