Hi Team,
I have a Pivot Table report linked up to a data model held in the Power BI Service. OLAP is used for fast querying.
I now need to present some of the data in a slightly different way and I'm wondering if MDX (in Excel) might be the solution.
Below is small example to try to illustrate what I'm trying to do...
Fact table is like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]View[/TH]
[TH]Month[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]A[/TD]
[TD]201901[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201902[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201903[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201904[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201901[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201902[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201903[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201904[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Dimension table is like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Month[/TH]
[TH]Short Month[/TH]
[/TR]
[TR]
[TD]201901[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]201902[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]201903[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]201904[/TD]
[TD]04[/TD]
[/TR]
</tbody>[/TABLE]
There is a relationship in place between the 'Month' columns within the data model.
My Pivot Table (Excel) is laid out like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
I want to create a new measure to 'pick out' values from A or B depending on the Short Month. For example, I might want data from A for Short Months 01, 02 and 04, but from B for Short Month 03. So I want a measure that will produce the following:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]Measure[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]
I've had a play with the MDX Calculate Measure option, using IIF and AND functions, as well trying CASE WHEN, but I can't quite get the measure to work with both the fields I need it to reference, i.e. 'Short Month' and 'View'.
Is this kind of thing achievable with MDX?
Cheers,
Matty
I have a Pivot Table report linked up to a data model held in the Power BI Service. OLAP is used for fast querying.
I now need to present some of the data in a slightly different way and I'm wondering if MDX (in Excel) might be the solution.
Below is small example to try to illustrate what I'm trying to do...
Fact table is like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]View[/TH]
[TH]Month[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]A[/TD]
[TD]201901[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201902[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201903[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]201904[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201901[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201902[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201903[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]201904[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Dimension table is like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Month[/TH]
[TH]Short Month[/TH]
[/TR]
[TR]
[TD]201901[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]201902[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]201903[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]201904[/TD]
[TD]04[/TD]
[/TR]
</tbody>[/TABLE]
There is a relationship in place between the 'Month' columns within the data model.
My Pivot Table (Excel) is laid out like this:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
I want to create a new measure to 'pick out' values from A or B depending on the Short Month. For example, I might want data from A for Short Months 01, 02 and 04, but from B for Short Month 03. So I want a measure that will produce the following:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Short Month[/TH]
[TH]Measure[/TH]
[/TR]
[TR]
[TD]01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]
I've had a play with the MDX Calculate Measure option, using IIF and AND functions, as well trying CASE WHEN, but I can't quite get the measure to work with both the fields I need it to reference, i.e. 'Short Month' and 'View'.
Is this kind of thing achievable with MDX?
Cheers,
Matty