Matrix Visual to Display Columns where Data Does not Exist

[Vishruti]

I have data (as given below) (also attached in Excel).

The data is about Products, Product Type and Quarter-Year.
For one product, there may be multiple Product Types for the same quarters or multiple quarters. Or for a particular product, there may not be any product type given for a particular quarter.

 
I want to create a Matrix visual with Product on Rows, Year-Quarter on Columns and Product Type in the Values.
I have a date slicer. Whichever range user selects from the date slicer, I want to show only 8 quarters in matrix visual based on the minimum date selected in the filter. (Min selected included).

The main problem is, if a particular quarter does not have any record for any product then that Quarter column is not displayed. I want this column also to be seen with either a blank space or "-" like below

 

Dummy data link - https://docs.google.com/spreadsheets/d/14aknAb9mBGgQwMongOs7QBkW45npasmMUYX-1_bhqrw/edit?usp=drivesd...

PS I have tried the "Show Items With No Data" setting but it did not work.

[rohit1991]

Hi @Vishruti 

1. The sample data that I used to solve this problem is shown below.

2. Create new Table :

QuarterTable = 
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                CALENDAR (DATE(2025,1,1), DATE(2027,12,31)),
                "Quarter_Year", "Q" & FORMAT(ROUNDUP(MONTH([Date])/3,0), "0") & "_" & YEAR([Date])
            ),
            "Quarter_Year", [Quarter_Year],
            "Year", YEAR([Date]),
            "QuarterNum", ROUNDUP(MONTH([Date])/3,0)
        )
    ),
    "SortOrder", [Year]*10 + [QuarterNum]
)

3. Go to Data view

   Select Quarter_Year column >> Sort by column >> SortOrder

4. Create Relationship Many to One.

5. Create Measure:

Product Type Display = 
VAR _type =
    SELECTEDVALUE ( 'Sample Data'[Product Type] )
RETURN
    IF ( ISBLANK ( _type ), "-", _type )

6. Click on your Matrix visual >> Right-click on QuarterTable[Quarter_Year] field in the “Columns” area  >> click on  "Show items with no data".

[nandic]

Hi @Vishruti ,

In data you need to have all expected values.
Example, in sheet "Data" there is no Quarter_Year "Q3_2025" and "Q3_2026".

As it is completelly missing, it can't be shown.

So there are two options:
1. add these columns in Data sheet
2. make data model a little bit more complicated, where you would have disconnected table with all needed values and then using DAX make it work nicely

But for this stage, i suggest the first option.

Cheers,
Nemanja

[ThxAlot]

There's a built-in optimization technique called "Auto-Exist", which is used by the filtering mechanisms of DAX.

Understanding DAX Auto-Exist - SQLBI

[Rupak_bi]

Hi @Vishruti ,

You need to make a master quarter table with all quarter names. here is your expected result below. attaching PBIX for referance.