Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Lookupvalue based on variable slicer selection showing blanks ?

Hi, I am trying to make a report in PowerPivot where I dynamically select a reporting currency, and with a variable measure this will multiply my amount in SEK to any currency that I select in the sl...
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous your formula looks ok not sure what is missig, why not replace following to var

 

selection = "CHF";SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];
'Dim Exchange rates'[Datekey];'Fact PL Data'[currency key date];'Dim Exchange rates'[Currency];selction));
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Could you explain what you are trying to do with the var function now?

     

    I managed to get it to work. I used lookupvalue on the wrong column which was not in the same format.

     

    However => Is there an easier or prettier way to make this formula variable? Cause now if I would have a new currency in my selection, I would have to manually update my formula here:

     

     

    =VAR selection = [Selected_Currency_reported]

    return

    switch(true();

    selection = "CHF";SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];'Dim Exchange rates'[Datekey];'Fact PL Data'[Datekey currency];'Dim Exchange rates'[Currency];[Selected_Currency_reported]));
    selection = "CZK"; SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];'Dim Exchange rates'[Datekey];'Fact PL Data'[Datekey currency];'Dim Exchange rates'[Currency];[Selected_Currency_reported]));
    selection = "EUR";SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];'Dim Exchange rates'[Datekey];'Fact PL Data'[Datekey currency];'Dim Exchange rates'[Currency];[Selected_Currency_reported]));
    selection = "GBP";SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];'Dim Exchange rates'[Datekey];'Fact PL Data'[Datekey currency];'Dim Exchange rates'[Currency];[Selected_Currency_reported]));
    selection = "HUF";SUMX('Fact PL Data';'Fact PL Data'[SEK amount]*LOOKUPVALUE('Dim Exchange rates'[Exch. Rate];'Dim Exchange rates'[Datekey];'Fact PL Data'[Datekey currency];'Dim Exchange rates'[Currency];[Selected_Currency_reported]));"Incorrect values")