Forum Discussion

wvadik's avatar
wvadik
Helper III
6 years ago
Solved

Stacked Column Chart like Matrix

Hi guys,   I have table facts with some data and next fields: Date, Field1....5 (dimension fields), Day1, Day2, Day3.... etc (measure fields - count some facts that happened since the Date on the ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi wvadik,

     

    here is an example for the first 10 days.

     

    Calculated Table:

     

     

    MeasureTable = SELECTCOLUMNS(GENERATESERIES ( 1, 100 ,1 ), "Measure", "Day" & [Value], "Sort ID", [Value])

     

     

    Measure:

     

     

    MeasureSwitch =
IF (
    HASONEVALUE ( MeasureTable[Measure] ),
    SWITCH (
        SELECTEDVALUE ( MeasureTable[Measure] ),
        "Day1", SUM ( facts[Day1] ),
		"Day2", SUM ( facts[Day2] ),
		"Day3", SUM ( facts[Day3] ),
		"Day4", SUM ( facts[Day4] ),
		"Day5", SUM ( facts[Day5] ),
		"Day6", SUM ( facts[Day6] ),
		"Day7", SUM ( facts[Day7] ),
		"Day8", SUM ( facts[Day8] ),
		"Day9", SUM ( facts[Day9] ),
		"Day10", SUM ( facts[Day10] ),
        BLANK (), BLANK ()
    )
)

     

     

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

  • mwegener's avatar
    mwegener
    6 years ago

    Hi wvadik ,

     

    you have to expand the hierachy on the axis.

    Currently blank is returned if there is no filter on the Measure "Dimension".

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

wvadik's avatar
wvadik
Helper III
6 years ago

Thanks mwegener 

I have a fact table with data for the last two years. In it, the data are calculated by day. For each day, calculations were made 100 days in advance. In total, in my fact table, there is one column with a start_date + 23 dimension columns that form groups and 100 measure columns (the sum for the group for each day is from 0 to 100 from the starting date of the group). All this in compressed form occupies 45 million rows. If I make an univot of 101 measure columns of days to turn into a table of the form: start_date + 23 measure columns + Day Number + Measure column for a specific day. That in this case, the size of the fact table that will need to be processed into a cube in the SSAS will be equal to 45 million * 101 = 4.5 billion rows. I think that the processing of such a table will be very long and will take a very long time.