Forum Discussion
ideas for make display data in matrix
i try to explain what my goal,
| month | amount | currency | amount converted in dkk |
| 1 | 1000 | dkk | 1000 |
| 2 | 500 | eur | 2000 |
my data are stored in table where sometimes currency is eur sometime dkk
i need to build matrix that will display eur or dkk
to do that i would like to have a kind of slicer or something else to select what currency will be displayed in the matrix
is that possible?
i just need the possibility to display a value or other in the matrix based on a selector
i got the idea to create a measure in dax that allow me to select 1 column or 2 column, and the put this measure in the value field of the matrix
https://community.powerbi.com/t5/Desktop/DAX-Column-content-based-on-slicer-selection/td-p/378628
can anyone see if it is possible and what dax query will allow this_?
7 Replies
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
If you'd like to select the currency in a slicer, I would suggest you create an independent [Currencies] table. Please download the demo from the attachment.
1. Create a new table.
Currencies = VALUES(Table1[currency])
2. Create a measure.
Measure = IF ( SELECTEDVALUE ( Currencies[currency] ) = "eur", SUMX ( FILTER ( 'Table1', 'Table1'[currency] = "dkk" ), [amount] ) / 4 + SUMX ( FILTER ( Table1, Table1[currency] = "eur" ), [amount] ), IF ( SELECTEDVALUE ( Currencies[currency] ) = "dkk", SUMX ( FILTER ( 'Table1', 'Table1'[currency] = "dkk" ), [amount] ) + SUMX ( FILTER ( Table1, Table1[currency] = "eur" ), [amount] ) * 4, SUM ( Table1[amount] ) ) )
Best Regards,
I modified your lines to make work for mine seems to work, the only "issue " is that if I want to display a company that is already in dkk and the selector dkk is not marked then the matrix is empty
it will be nice to have the possibility that when local currency = DKK the display selecting automatically both local currency and dkk
can I modify the below code to have that?
Measure =IF (SELECTEDVALUE ( CurrencySelector[value] ) = "DKK",SUMX ( FILTER ( UploadAccountsCurrentyPeriod, 'UploadAccountsCurrentyPeriod'[Currency selector] = "DKK" ), UploadAccountsCurrentyPeriod[PeriodAmountDKK] ),IF (SELECTEDVALUE ( CurrencySelector[value] ) = "Local Currency",SUMX ( FILTER ( 'UploadAccountsCurrentyPeriod', UploadAccountsCurrentyPeriod[Currency selector] = "Local currency" ), UploadAccountsCurrentyPeriod[Period Amount] ),SUM(UploadAccountsCurrentyPeriod[PeriodAmountDKK])))I tried this combo but i cant make it work
Measure-total =IF (SELECTEDVALUE ( CurrencySelector[value] ) = "DKK",SUMX ( FILTER ( UploadAccountsCurrentyPeriod, 'UploadAccountsCurrentyPeriod'[Currency selector] = "DKK" ), UploadAccountsCurrentyPeriod[-amount] ),IF (SELECTEDVALUE ( CurrencySelector[value] ) = "Local Currency",SUMX ( FILTER ( 'UploadAccountsCurrentyPeriod', UploadAccountsCurrentyPeriod[Currency selector] = "Local currency" ), UploadAccountsCurrentyPeriod[-amount] ),IF ( SELECTEDVALUE(CurrencySelector[value]) = "Local Currency" && IF(LOOKUPVALUE(UploadAccountsCurrentyPeriod[Currency selector],UploadAccountsCurrentyPeriod[Currency selector], "DKK"), SUM(UploadAccountsCurrentyPeriod[-amount] )))))