Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 .
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.
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.
Hi @Paulyeo11 ,
Here's one idea of how it can be done using a bridging table.
Could well be other ways of handling this .
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.
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.
Mr Teo
Thank you very much . you are good. you from which country ?
@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
As far as I understand you want to keep 2 columns .
Cross the colums whichever you dont want in table
Hi Nikhi
Can you help me create sample PBI File like below :-
https://www.dropbox.com/s/jw9mtje067y731q/DYNAMIC%20MEASURE%20V002.pbix?dl=0
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |