Forum Discussion

Vishruti's avatar
Vishruti
Helper I
9 months ago

Matrix Visual to Display Columns where Data Does not Exist

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=drivesdk

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

Show and Tell

4 Replies

  • nandic's avatar
    nandic
    Resident Rockstar
    9 months ago

    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

  • rohit1991's avatar
    rohit1991
    Super User
    9 months ago

    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".