topic Re: Replace SELECTEDVALUE to something else in DAX Commands and Tips

Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 15:03:53 GMT

Hi All!

i have this Dax:

Gross USD =

IF (

SELECTEDVALUE( 'db 2021'[Source]) = "Amazon",

CALCULATE (

DIVIDE (

( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ),

COUNTROWS ( 'Date' )

KEEPFILTERS ( 'Date'[Date] < TODAY () )

CALCULATE (

DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ),

KEEPFILTERS ( 'Date'[Date] < TODAY () )))

My problem is, the formula only works when i filter Amazon
I want it to work whenever source = Amazon

Just need to replace SELECTEDVALUE right?

Please Help

Re: Replace SELECTEDVALUE to something else

Samarth_18 — Mon, 10 Jan 2022 15:06:58 GMT

Hi netanel ,

You can try below code:-

Gross USD = IF ( MAX( 'db 2021'[Source]) = "Amazon", CALCULATE ( DIVIDE ( ( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ), COUNTROWS ( 'Date' ) ), KEEPFILTERS ( 'Date'[Date] < TODAY () ) ), CALCULATE ( DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ), KEEPFILTERS ( 'Date'[Date] < TODAY () )))

Thanks,

Samarth

Re: Replace SELECTEDVALUE to something else

ValtteriN — Mon, 10 Jan 2022 15:33:15 GMT

Hi again 😁,

When using selectedvalue in a measure like in your example the logic is applied in the filter context. E.g. here only customer A's Dates are shown as blank:
(edit: I forgot to add my example. Here the source column -> in this case customer, is not acting as a filter. netanel 😞

If you want to remove the filter context you can use functions like ALL or REMOVEFILTERS. Based on your example the measure should work? What am I missing here?

Edit 2:

Here is an example on how to do this kind of logic in a column:

IFColumn = IF('Matrix example'[Brand]="Wood",'Matrix example'[Value],'Matrix example'[Value]+'Matrix example'[Value2])

Re: Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 15:19:48 GMT

HI Samarth_18

MAX Brings me the same results...

Re: Replace SELECTEDVALUE to something else

ValtteriN — Mon, 10 Jan 2022 15:20:55 GMT

Just to clarify is the DAX you use a measure or a column?

Re: Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 15:24:35 GMT

Hi again 😉 ValtteriN

You're right,
But I do not use a source column as a filter

I just want that every time that Source = to Amazon
Then connect the Fee column to the Gross column

Re: Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 15:26:15 GMT

Hi ValtteriN

a Column

Thanks for your time!

Re: Replace SELECTEDVALUE to something else

ValtteriN — Mon, 10 Jan 2022 15:36:46 GMT

netanel

So now that I know it is a column a simple IF logic should be fine. As you can see in my example above, if you are creating a calculated column there is no need for MAX/SELECTEDVALUE. e.g.

IF (

'db 2021'[Source] = "Amazon",

CALCULATE (

DIVIDE (

( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ),

COUNTROWS ( 'Date' )

KEEPFILTERS ( 'Date'[Date] < TODAY () )

CALCULATE (

DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ),

KEEPFILTERS ( 'Date'[Date] < TODAY () )))

Re: Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 15:43:05 GMT

Hi ValtteriN

Thanks I think it's really close ...

Re: Replace SELECTEDVALUE to something else

Samarth_18 — Mon, 10 Jan 2022 16:06:36 GMT

netanel just remove source from this line 'db 2021'[Source] = "Amazon", and click on ctrl+space bar it will suggest column names then select source column from there. It will work.

Re: Replace SELECTEDVALUE to something else

netanel — Mon, 10 Jan 2022 16:53:11 GMT

Samarth_18

It only offers me formula...

And I do not understand why

Re: Replace SELECTEDVALUE to something else

netanel — Tue, 11 Jan 2022 08:27:28 GMT

Hi ValtteriN Samarth_18

Sorry friends,
I was thinking in the wrong direction and a bit complicated it.
I went backwards and the direction of ValtteriN is the right direction
The Gross formula works well so I'll just add the Fee to it whit +
The problem is I only need the Fee when the Source is "Amazon"
So that's what I've put together
The second formula gets Blank
Why?

Gross USD =

CALCULATE(

DIVIDE( SUM( 'DB 2021'[Gross USD] ), COUNTROWS( 'Date' ) ),

keepfilters( 'Date'[Date] < TODAY()))

2. Measure ValtteriN

Amazone Fee = IF (SELECTEDVALUE('DB 2021'[Source]) <> "Amazon", BLANK (), SELECTEDVALUE('DB 2021'[Fee]))

If I connect the two formulas I get nothing
Because the second formula brings me Blank

Re: Replace SELECTEDVALUE to something else

ValtteriN — Tue, 11 Jan 2022 09:01:52 GMT

Like Samarth said you should get the column as a suggestion. Are you using a measure now? If so you here is a completely new approach to this:

CALCULATE (

DIVIDE (

( SUM ( 'DB 2021'[Gross USD] ) + SUM ( 'DB 2021'[Fee USD] ) ),

COUNTROWS ( 'Date' )

KEEPFILTERS ( 'Date'[Date] < TODAY () ),filter('db 2021','db 2021'[Source] = "Amazon"))

CALCULATE (

DIVIDE ( SUM ( 'DB 2021'[Gross USD] ), COUNTROWS ( 'Date' ) ),

KEEPFILTERS ( 'Date'[Date] < TODAY () ),filter('db 2021','db 2021'[Source] <> "Amazon"))

The logic here is to use one calculate logic when customer is Amazon (include the fee there) and other when it is not.

Re: Replace SELECTEDVALUE to something else

netanel — Tue, 11 Jan 2022 09:19:15 GMT

ValtteriN

Thank you!
It works great
Thanks for the time i really appreciate it!

😊