Forum Discussion
Replace SELECTEDVALUE to something else
Hi All!
i have this Dax:
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
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.
13 Replies
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
Just to clarify is the DAX you use a measure or a column?
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])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 () )))
