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 ,
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.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".
