Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simrantuli
Continued Contributor
Continued Contributor

Sorting a column having NULLs

Hi All,

 

I have a requirement to create below table as matrix in Power BI.

simrantuli_0-1601401136096.png

 

I have two tables in my data model which look like below.

simrantuli_1-1601401187689.png

 

Fact Table:

simrantuli_2-1601401214895.png

 

KPIs table:

simrantuli_3-1601401249783.png

 

The matrix that I have created looks like below:

simrantuli_4-1601401309579.png

However, the issue is that I don't know how to sort 'Type' column. Basically, under 'Headcount', Regular should come first and then Non Regular. Similarly for Desk. And under Capacity, the order should be A, Z, E ,B.

How do I achieve this?

 

Your help would be greatly appreciated.

 

Thanks!

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi @simrantuli ,

 

First create a column as below:

Column = LOOKUPVALUE('KPIs table'[SO],'KPIs table'[KPI],'Fact Table'[KPI],0)

Then duplicate column KPI,named as _KPI,select _KPI,choosing sort by column:

Screenshot 2020-10-07 130101.png

And you will see:

Screenshot 2020-10-07 130205.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Hi @v-kelly-msft,

 

Thanks for your contribution.

Is it possible to sort the column 'Type'? Let's say under 'Capacity', I want the 'Type' to be in the following order: B, A. Z & E.

 

Best Regards

Simran Tuli

Hi @simrantuli ,

 

Based on my test,it cant be reached,as the sorting is illogic,I cant find a suitable calculation to achieve it.

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

FrankAT
Community Champion
Community Champion

Hi @simrantuli 

in Power Query add two columns for sorting. Use in tab Column tools button Sort by column (see figure 1 & 2):

 

29-09-_2020_23-47-16.png

 

29-09-_2020_23-49-18.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

sreenub
Resolver II
Resolver II

sreenub_0-1601402405787.png

 

is this expected result , to acive this just sort by type in matrix visual

screenshot.jpg

With  Totals 

 

Tahreem24
Super User
Super User

Hi Simran,

Your requirement is not in chronological order. For this custom order you can create one calculated table with the help of Switch and this your type column using that calculated column:

Calculated Column = SWITCH(Table[Type], "Regular", 1,"Not Regular", 2," A", 3,"Z", 4, "E", 5,"B", 6)

Then go to the data view tab ( just above  tab ) then select "Type" Column then choose a "Sort By Column" option from Menu then select the above created custom column (I.e. Calculated Column).

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors