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,
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
, DayNumber
These 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
.....
, dimension23
So 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.
mwegener, Thanks. Greate work.
This is very close to the result, but my stacked column chart should have two dimensions on the axis in addition to the day numbers. If I add one more dimension to the "Axis" (Field1 in your example) in addition to the SortID field already there (and in my case, I need to add two more measurements to the "Axis"), then the stacked column chart shows emptiness. I suspect this is because the calculated MeasureTable table is not dimensionally related, but I could be wrong. I attach your example with the added field to the "Axis".