Forum Discussion

jmeyer348's avatar
jmeyer348
Frequent Visitor
1 year ago
Solved

matrix visual column range

Hi everyone!         I'm having a bit of an issue with a matrix visual that I'm trying to create.  I've reproduced a simpliefied version of the issue in the image below.  Basically, I want the year-...
  • jmeyer348's avatar
    jmeyer348
    1 year ago

    Kedar_Pande , Solved! I used your method, except I made one change.  I updated the "Usage" measure to return 0 if it did not find any rows to do the calculation.  Here is the DAX code:  

    Usage =
    VAR Last12Months =
    CALCULATE(
        SUM(Sheet1[Total Usage]),
        FILTER(
            DateTable,
            DateTable[Date] >= EDATE(TODAY(), -12) && DateTable[Date] < TODAY()
        )
    )
    RETURN
    IF(ISBLANK(Last12Months), 0, Last12Months)
     
    Once I made this change, I was able to see rows that did not have any usage in the last 12 months.

     

NordicDrow's avatar
NordicDrow
Helper I
1 year ago

The first idea that comes to mind is just creating a duplicated table, just keep the material and Main Customer column (for ease of use), relate by Customer, Material or generate a new column in each table that would basically be  a concat of this columns (single direction relationship, otherwise the new table will also be filtered), use the new table material and customer columns in rows.


Its not ideal, but if I understand your needs it should work, matrixes are a bit hard to work with, they are quite limited. The best way otherwise would be using a measure ALL(Material) but it does not work like this.