Stacked Column Chart like Matrix

[wvadik]

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 first day, second day, third day etc).

In my report I need Matrix and Stacked Column Chart. With the matrix, everything worked out.

On the Stacked Column Chart I need something similar: each column should display one day (mesures Day1, Day2 ... etc) on Axis.

I know that the source data should be in a different format for this chart:

Date, Field1....5, DayNumber, DayQnt (measure field)

But the source data is large enough, and if I transform it into this format, then the size of the fact table will be about 4 billion rows.
Therefore, I am looking for a way to solve my problem using visualization in the report.

How to create the one I need Stacked Column Chart ?

[mwegener · Accepted solution]

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]

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.

[wvadik]

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.

[v-chuncz-msft]

@wvadik 

You may try creating a measure based on what-if parameters.

Parameter = GENERATESERIES(0, 100, 1)

[mwegener]

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?

[mwegener · Accepted solution]

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.

[wvadik]

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.

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]

Hi @wvadik ,

I think we understood you correctly.

You may download my PBIX file from here.
Hope this helps.

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

Please give Kudos for support.

[wvadik]

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

[mwegener · Accepted solution]

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]

@mwegener, Thank you VERY VEDRY match 🙂

It's greate!