Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Slicer query in Power BI

Hi, I have a table which has data in ‘000 INR. (Pls. refer ‘RawData’ Query). I would like to create a slicer in BI and give the user an option to slice data by the following: LC in '000 LC in Crs ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    I made one sample for your referecen. To create a measure as below.

     

    Measure = var sele = SELECTEDVALUE(EditedData[Select Currency])
var amount = SUM(RawData[Value])
return
IF(ISBLANK(sele),BLANK(),IF(sele="in US$ '000",amount/6,IF(sele="in US$ Mns",amount/100,IF(sele="LC in '000",amount,amount/3))))

     

    For more details, please check the pbix as attached.

     

    Regrads,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

Why did you not add the column of EditedData to slicer directly? Or could you please describe your request more specificly?

 

 

Regards,

Frank

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Frank,

     

    Thank you for your reply. 

    My data source is in excel, that has the RawData tab. All the values are in INR '000s by default.

    Because our stakeholders are in India as well as Internationally, I need to give the users an option to see the numbers/values in:

    INR '000s,

    INR Crs (which is equivalent to 10,000,000 i.e., 10Mn)

    US$ '000s,

    US$ Mns

     

    Currently, I have to convert all INR values into USD and divide by '000 or Mns to represent the numbers accordinlgy. All this is done in Excel in Edited Data Tab. Thus, a separate column is maintained i.e., 'Select Currency' to show this distinction.

     

    To do the above, I have to triplicate same set of data in Excel, EditedData tab, and then in BI I have a slicer which is based on 'Select Currency' column. However, every month, new data is added in Excel and it makes my file very heavy and I do not wish to use this method going forward for other reasons as well. 

     

    I was wondering if there is a way that in BI, once I get my raw data which is by default in '000 INR, I can use a measure or calculated column, that converts into US$ as well as be able to give the user an option to see the data either in INR '000s, INR Crores (10 Million), US$ '000, US$ Mns.

     

    Appreciate all your help in resolving my query.

    Thank you,

    Deepak.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-frfei-msft