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.
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.
Thanks mwegener , v-chuncz-msft
I will explain the original task and requirements.
Raw data are in ms sql. Prepared and calculated data for use in the report in PowerBi are loaded into a tabular model in ssas.
The initial data sample in which 5 billion rows:
select date_start , dimension1 , dimension2 , dimension3 ..... , dimension23 , DayNumber --the number of days from the date_start (from 0 to 100; the date_start (DayNumber = 0), 1 day from the date_start, 2 days from the date_start .... 100 days from the date_start) , count(*) as cnt from SourceTable group by date_start , dimension1 , dimension2 , dimension3 ..... , dimension23 , DayNumberThese data must be processed into a tabular model in ssas, building a model and indicating links to dim tables. Further, according to the data of this cube, build a report in power bi.
In the report, I need to create two visual elements, a matrix and a stacked column chart.
5 billion rows are very much for processing on our ssas server. processing such a volume does not work.
I compressed the data for the model using ms sql, preparing them for the fact table in the following form:
select date_start , dimension1 , dimension2 , dimension3 ..... , dimension23 , sum(iif(DayNumber = 0, cnt, 0)) as Day_0 , sum(iif(DayNumber = 1, cnt, 0)) as Day_1 , sum(iif(DayNumber = 2, cnt, 0)) as Day_2 , sum(iif(DayNumber = 3, cnt, 0)) as Day_3 ..... , sum(iif(DayNumber = 100, cnt, 0)) as Day_100 from SourceTable group by date_start , dimension1 , dimension2 , dimension3 ..... , dimension23So I got 45 million rows in the fact table.
In the report, I created a matrix, where I put dimension1 and dimension2 in the "Rows", I did not put anything in the "Columns". In the "Values" I put the received measures Day0, Day1, Day2 ... Day100. Matrix done and work as required.
But for the Stacked Column Chart, the fact table is needed in the original version (select date_start, dimension1, dimension2, dimension3, ....., dimension23, DayNumber , count(*) as cnt ), which contains 5 billion rows that do not fit in the cube.
In the stacked column chart: on the “Axis” I have to put dimension1 , dimension2 and DayNumber; on the "Legend" I have to put dimension2 ; on "Value" I have to put the measure "cnt". X-axis is DayNumber.
But in my fact table, instead of DayNumber and one measure ("cnt"), there are 101 single columns with measures obtained to reduce the size of the data contained in the table.
And I'm looking for a way for my fact table to create a visualization of a stacked column chart. The original stacked column chart allows you to put only one measure in “Value”, and I have 101 of them. Therefore, I am looking for either a custom visualization for such a histogram or a way using dax scripts to reformat the data to a format suitable for the original stacked column chart.