Dynamic Slicer query in Power BI

[Anonymous]

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

In US$ '000

In US$ Mns

Currently am duplicating data in excel to show the different numbering system in BI report (Pls. refer ‘EditedData’ Query).

'EditedData' tab has the result I expect to see in 'RawData' tab but without duplicating data in excel which is my data source.

Is there a way I can do this in BI dynamically (in ‘RawData’ Query) without me replicating the data in excel using any measure? If yes, would appreciate if someone could guide me on how to do the same?

Below is a link to download the PBIx file.

https://www.dropbox.com/s/eicajl3a6tp6443/Report%20Numbering.pbix?dl=0

Many thanks,

Deepak.

[v-frfei-msft · Accepted solution]

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

[Anonymous]

Hi @v-frfei-msft,

Thank you so much for your help. This is exactly what I was looking for.

Thanks once again.

Regards,

Deepak.

[v-frfei-msft]

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]

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]

Hi @v-frfei-msft

[Anonymous]

Hi all,

Could anyone pls. help me with the posted query?

Many thanks,

Deepak.