cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver IV
Resolver IV

Displaying Multiple Currency Types in a Matrix

I've been having a very difficult time with this problem and hoping someone can shed some light my way.

I have matrix which shows ProductNames by Row, Websites they are sold on by Column and the MAX Price they are sold at on that website as depicted in (Image 1).

 

I've now been tasked to apply the currency type at the beginning of the values in the Matrix.

Currency type is another column in the same fact table.

 

I've tried the following:

1. I've tried the solution in this link https://blog.enterprisedna.co/format-currencies-in-power-bi-using-dax/.

but having a slicer in the page to filter out currency types is not an option.

I've thought of having the slicer hidden in the page with all currency types selected but unfortunetly when more than 1 is selected it defaults all of them to USD.

2. Calculated column in summarized table and sort by sell price then change aggregation in value field to last. (This gave incorrect results when it came to blank products. This also is not the ideal solution to begin with.

 

I want to avoid using calculated columns in the fact table as it is millions of rows in size.

Sometimes Currency Types shows up as blank() so I would need it to default to USD in the Matrix what happens.

Possible currency types are (USD, MXN,CAD,BLR)

 

My intended results are depicted below in Image 2

 

Fact Table columns shown in Image 3

 

Relationship between Dim Products and Fact Table showin in Image 4

 

Image 1 

Capture.PNG

 

Image 2

Capture3.PNG

 

Image 3

Capture1.PNG

 

Image 4

Screenshot 2020-10-21 173425.png

 

Reaching out to the best in hopes of some DAX wizardry @Greg_Deckler 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Figured out the performance issue. Kind of embarrased it was so simple:

The original concatenate formula i mentioned in the first reply was concatenating every cell, even blanks which resulted in ppor performance.

adjusting the formula to the following gave me my intended results with good performance:

IF (
    NOT ( ISBLANK ( [MAX Price] ) ),
    CONCATENATE (
        MAX ( 'fact table'[Currency] ) & " ",
        ROUND ( [Max Price], 2 )
    ),
    BLANK ()
)

View solution in original post

2 REPLIES 2
Highlighted
Resolver IV
Resolver IV

Update:

 

I've also tried using a simple concatenate measure:

Concatenate( MAX( 'fact table'[Currency])&" ", Round([Max Price], 2)

 

This gave me my intended results but at a huge cost:

This resulted in Dax Query Timing of 37,000 MS. 

Highlighted
Resolver IV
Resolver IV

Figured out the performance issue. Kind of embarrased it was so simple:

The original concatenate formula i mentioned in the first reply was concatenating every cell, even blanks which resulted in ppor performance.

adjusting the formula to the following gave me my intended results with good performance:

IF (
    NOT ( ISBLANK ( [MAX Price] ) ),
    CONCATENATE (
        MAX ( 'fact table'[Currency] ) & " ",
        ROUND ( [Max Price], 2 )
    ),
    BLANK ()
)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors