3 Dim only display 2 Dim possible ?

[Paulyeo11]

Hi All

My raw data have total 3 dimension. I only want to display 2 Dimension , it is possible ?

My PBI File :-

https://www.dropbox.com/sh/9n26wjsdpricd6r/AADwP1QFNd5PXrb4KI86W-Iya?dl=0

Paul Yeo

[v-stephen-msft · Accepted solution]

Hi @Paulyeo11 ,

Here's one idea of how it can be done using a bridging table.

Could well be other ways of handling this .

Dummy Power BI model here.

Assuming you have Country and FRUIT lookup tables, create a CountryFRUIT table which is the cross product of Country & FRUIT tables.

Duplicate each row of CountryFRUIT and add an Axis Dimension column which is " Country " for half the rows and  " FRUIT " for the other half, and an Axis Value column which is the Country or FRUIT value for each row (depending on the Axis Dimension value).

Relate Country and FRUIT to CountryFRUIT using inactive bidirectional relationships:

Create an Axis Dimension Selected measure to harvest the value of Axis Dimension. Something equivalent to this (this guards against multiple selection):

Axis Dimension Selected = 
IF (
    ISFILTERED ( 'CountryFRUIT'[Axis Dimension] ),
    IF (
        CALCULATE ( HASONEVALUE ( 'CountryFRUIT'[Axis Dimension] ), ALLSELECTED () ),
        VALUES ( 'CountryFRUIT'[Axis Dimension] )
    )
)

Create a Sales Amount Flexible Axis measure like this (assuming Sales Amount is the normal measure):

Sales Amount Flexible Axis = 
IF (
    NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
    SWITCH (
        [Axis Dimension Selected],
        "Country",
        CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( 'CountryFRUIT'[Country], 'Country'[COUNTRY])
        ),
        "FRUIT",
        CALCULATE (
           [Sales Amount],
            USERELATIONSHIP ( 'CountryFRUIT'[FRUIT], 'FRUIT'[FRUIT] )
        )
    )
)

Then you can create visualizations using RegionYear[Axis Value] and [Sales Amount Flexible Axis]

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-stephen-msft · Accepted solution]

Hi @Paulyeo11 ,

Here's one idea of how it can be done using a bridging table.

Could well be other ways of handling this .

Dummy Power BI model here.

Assuming you have Country and FRUIT lookup tables, create a CountryFRUIT table which is the cross product of Country & FRUIT tables.

Duplicate each row of CountryFRUIT and add an Axis Dimension column which is " Country " for half the rows and  " FRUIT " for the other half, and an Axis Value column which is the Country or FRUIT value for each row (depending on the Axis Dimension value).

Relate Country and FRUIT to CountryFRUIT using inactive bidirectional relationships:

Create an Axis Dimension Selected measure to harvest the value of Axis Dimension. Something equivalent to this (this guards against multiple selection):

Axis Dimension Selected = 
IF (
    ISFILTERED ( 'CountryFRUIT'[Axis Dimension] ),
    IF (
        CALCULATE ( HASONEVALUE ( 'CountryFRUIT'[Axis Dimension] ), ALLSELECTED () ),
        VALUES ( 'CountryFRUIT'[Axis Dimension] )
    )
)

Create a Sales Amount Flexible Axis measure like this (assuming Sales Amount is the normal measure):

Sales Amount Flexible Axis = 
IF (
    NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
    SWITCH (
        [Axis Dimension Selected],
        "Country",
        CALCULATE (
            [Sales Amount],
            USERELATIONSHIP ( 'CountryFRUIT'[Country], 'Country'[COUNTRY])
        ),
        "FRUIT",
        CALCULATE (
           [Sales Amount],
            USERELATIONSHIP ( 'CountryFRUIT'[FRUIT], 'FRUIT'[FRUIT] )
        )
    )
)

Then you can create visualizations using RegionYear[Axis Value] and [Sales Amount Flexible Axis]

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Paulyeo11]

[v-stephen-msft · Accepted solution]

Hi @Paulyeo11 ,

Please click on the hyperlink in the red box to download PBIX file. 

Then you will see the Measure formula in the Fields.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Paulyeo11]

Mr Teo

Thank you very much . you are good. you from which country ?

[v-stephen-msft]

Hi @Paulyeo11 ,

Very happy to help you.

I am from China.

Best Regards,

Stephen Tao

[amitchandak]

@Paulyeo11 , This can be done using bookmarks and buttons. https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Or you can refer to:

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M

[Anonymous]

As far as I understand you want to keep 2 columns .

Cross the colums whichever you dont want in table

[Paulyeo11]

Hi Nikhi

Can you help me create sample PBI File like below :-

https://www.dropbox.com/s/jw9mtje067y731q/DYNAMIC%20MEASURE%20V002.pbix?dl=0