Forum Discussion

Vishruti's avatar
Vishruti
Icon for Helper I rankHelper I
10 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 mul...
Show and Tell
rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
10 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".