Forum Discussion
Stacked Column Chart like Matrix
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.
Hi wvadik ,
look at this. "Less Columns, More Rows = More Speed!"
https://powerpivotpro.com/2011/08/less-columns-more-rows-more-speed/
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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.
Hi wvadik ,
I don't understand your data model in detail, but a data model with100 measure columns don't sound healthy to me.
Do you really need to precalculate all of these measures in your fact table?
Or could these calculations also be based on movement data?
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.
