Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Calculated column that changes according to a filter in the Report section

Hi everyone,

 

I am trying to make a calculated column in Power BI that changes according to the filter in a report, for example:

 

I have this two tables in my Data section:

 

Table A

PersonMoney
x$ 123
y$ 198
z$ 98

 

Table B

CurrencyCost
Pounds$ 16,70
Euro$ 2

 

In my report Section I put Currency as a report filter, in order to let the user see the Money in the currency he or she wants.

 

So I want to create a New Column in Table A, that calculates: Table A[Money] /  Table B[Cost]. I am not being able to do this calculation, because the Data section does not recognize the filter I use in the report.

 

Is there any solution to my problem?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @MariaFlorenciaB,

 

 I think yes, we coud use calculated column but there would be a minor bug when users select multiple options in slicer:

  • I crossjoin 2 table Fact & Currency rate table by create calculated table:

 

Cal table = CROSSJOIN('Transaction','Currency')
Sales Volume = DIVIDE('Cal table'[Money],'Cal table'[Cost])

 

Screenshot 2016-12-28 20.46.14.pngScreenshot 2016-12-28 20.46.22.png

 

(in the second picture, there is wrong result that we could not handle or hide)

 

View solution in original post

3 REPLIES 3
tringuyenminh92
Memorable Member
Memorable Member

HI @MariaFlorenciaB,

 

 

You could achieve that by DAX:

  • Create calcuated measure:

 

Sale Volume = if(HASONEVALUE('Currency'[Cost]),DIVIDE( sum('Transaction'[Money]),FIRSTNONBLANK('Currency'[Cost],'Currency'[Cost])),BLANK() )

 

Screenshot 2016-12-28 19.50.39.png

 

 

i used hasonevalue() to check when multiple currency rates selected, the division value will be blank(). cause if there is not this checking, the result value in Card control will be wrong (this is applied showing in card control)

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

 

 

 

 

Thanks @tringuyenminh92 for your answer.

 

Is there any posibilitiy to do this calculation in a Calculated Column and not in a Measure?

 

Thanks!!

Hi @MariaFlorenciaB,

 

 I think yes, we coud use calculated column but there would be a minor bug when users select multiple options in slicer:

  • I crossjoin 2 table Fact & Currency rate table by create calculated table:

 

Cal table = CROSSJOIN('Transaction','Currency')
Sales Volume = DIVIDE('Cal table'[Money],'Cal table'[Cost])

 

Screenshot 2016-12-28 20.46.14.pngScreenshot 2016-12-28 20.46.22.png

 

(in the second picture, there is wrong result that we could not handle or hide)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.