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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
fa82
Regular Visitor

Column goes missing from matrix when using sort order table

Hi Everyone, I'm quite stuck on something.

My data has Year, Quarter, Category, Sub-category, and Value

When added to a matrix, I have a custom column order for the years using  a seperate YearOrder table:

fa82_0-1640254053882.png

 

When adding the 'Year' column to the matrix from the sortOrder table, one of the years goes missing (2022 Actuals). If I replace it with the 'Year' from the original table, the year shows up again. Why is it going missing? I've triple checked spelling- I have no idea why it's behaving this way. Am I missing something?

I'm not having any issues with missing columns in other matrices using the same YearOrder table.

 

8 REPLIES 8
bcdobbs
Community Champion
Community Champion

Is it possible there's a hidden space or carriage return coming in on your main table?

 

If you go into power query, right click the column and use the "TRIM" option and just to be sure "CLEAN" option in the transform to throw away any weirdness I'm hoping it might spring into life. 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Your solution makes sense but unfortunately it didn't work . I cleaned and trimmed both YearOrder and the data table but the column is still missing

bcdobbs
Community Champion
Community Champion

Not an obvious one!

Few questions on things to check:

 

Is anything lurking in the report/page filters?

 

Are any other visuals/slicers interacting?

 

Are you using a measure in the matrix. If so can you share. Wondering if it is returning blank for some reason.

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

1. There are no filters

2. I put the visual in a new page - nothing is interacting with it

3. There are no measures:

fa82_0-1640263848299.png

 

I filtered the visual using YearOrder table to only show the missing column and the matrix returns a blank:

fa82_2-1640263933049.pngfa82_1-1640263914918.png

 

bcdobbs
Community Champion
Community Champion

Just realised it's 2022 Actuals...

 

Presumably you have no data for 2022. Value is therefore blank so it will hide.

 

I think if you click arrow on "Year" in the column section there should be a show blanks. (Will check wording when next on a computer)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Community Champion
Community Champion

Try this:

bcdobbs_0-1640273104901.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

I have dummy data in 2022 Actuals for testing purposes. When I select show items with no data, 2022 Actuals shows up but is indeed blank! When I replace 'Year' in the matrix with the one from the data table, 2022 Actuals column shows with the dummy data, so the mystery remains

with Year from YearOrder table

fa82_0-1640510443268.png

with Year from Data table

fa82_1-1640510532291.png

 

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.