Reply
Highlighted
Frequent Visitor
Posts: 7
Registered: ‎10-23-2018
Accepted Solution

Calculate measurment Dynamicly based on filters

Hi, all I'm new to Dax,

I would like to use a calculation function to multiple column values with another column values of the next day to calculate "ARPU_D1"
I tried to use a filter for it.

The calculation that I want to translate to a Dax formula is:

"ARPU_D1" = "retention" * "Arpdau_all_col("install_date" + 1)" grouped by ("install_date", "app", "country".

For example for the first row in the table.
"retention" = 0.653846154 
Arpdau_all_col("install_date" + 1) = $0.0157

ARPU_D1 = 0.653846154 * $0.0157

This calculation should be applied for each row in the table.  

The rows that were used for the example is marked in blue and the column values from these rows are marked in red.

install_dateappcountryArpdau_all_cold0d1retentionARPU_D0ARPU_D1
15/10/2018 00:00app1Germany$0.017226170.653846154$0.0172 
15/10/2018 00:00app1United States$0.040131210.677419355$0.0401 
15/10/2018 00:00app2Germany$0.02972341140.487179487$0.0297 
15/10/2018 00:00app2United States$0.0600261714500.554069545$0.0600 
16/10/2018 00:00app1Germany$0.01572180.380952381$0.0157 
16/10/2018 00:00app1United States$0.048025291.16$0.0480 
16/10/2018 00:00app2Germany$0.02272911610.553264605$0.0227 
16/10/2018 00:00app2United States$0.0489267119310.722950206$0.0489 
17/10/2018 00:00app1Germany$0.020825100.4$0.0208 
17/10/2018 00:00app1United States$0.066448250.520833333$0.0664 
17/10/2018 00:00app2Germany$0.02564152070.498795181$0.0256 
17/10/2018 00:00app2United States$0.0569405323640.583271651$0.0569 



How would I be able to do this?

Thanks


Accepted Solutions
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Calculate measurment Dynamicly based on filters

try this code

Column =
VAR App = 'Table'[app]
VAR Country = 'Table'[country]
VAR CurrentDate = 'Table'[install_date]
RETURN
    'Table'[retention]
        * CALCULATE (
            MAX ( 'Table'[Arpdau_all_col] ),
            FILTER (
                'Table',
                'Table'[app] = App
                    && 'Table'[country] = Country
                    && 'Table'[install_date] = CurrentDate + 1
            )
        )

View solution in original post


All Replies
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Calculate measurment Dynamicly based on filters

try this code

Column =
VAR App = 'Table'[app]
VAR Country = 'Table'[country]
VAR CurrentDate = 'Table'[install_date]
RETURN
    'Table'[retention]
        * CALCULATE (
            MAX ( 'Table'[Arpdau_all_col] ),
            FILTER (
                'Table',
                'Table'[app] = App
                    && 'Table'[country] = Country
                    && 'Table'[install_date] = CurrentDate + 1
            )
        )
Super User
Posts: 770
Registered: ‎09-16-2018

Re: Calculate measurment Dynamicly based on filters

Hi @eli_p

 

 

I built the below mode:

 

Capture.PNG

 

 

and then used this measure:

 

 

ARPU_D1 =
SUMX (
    SUMMARIZE (
        Data,
        'Calendar'[Date],
        Apps[app],
        Countries[country]
    ),
    CALCULATE (
        SUM ( Data[Arpdau_all_col] ),
        NEXTDAY ( 'Calendar'[Date] )
    ) * CALCULATE (
            SUM ( Data[retention] )
        )
)

 

Capture.PNG



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Frequent Visitor
Posts: 7
Registered: ‎10-23-2018

Re: Calculate measurment Dynamicly based on filters

Thanks for the reply. 

it seems that this expression won't work for me because the  'app' , 'country' , 'install_date' are not measurements.
 

I'm receiving this error.

The syntax for ''[app_id]'' is incorrect. (DAX(VAR App = LTV'[app_id]'VAR Country = 'LTV'[country]VAR CurrentDate = 'LTV'[install_date]RETURN 'LTV'[d1/d0] * CALCULATE ( MAX ( 'LTV'[Arpdau_all_col] ), FILTER ( 'LTV', 'LTV'[app] = App && 'LTV'[country] = Country && 'LTV'[install_date] = CurrentDate + 1 ) ))).

Super User
Posts: 866
Registered: ‎06-23-2016

Re: Calculate measurment Dynamicly based on filters

there is typo in your syntax
LTV'[app_id]'

instead of

'LTV'[app_id]

Frequent Visitor
Posts: 7
Registered: ‎10-23-2018

Re: Calculate measurment Dynamicly based on filters

@Stachu 

Sorry, I did not mention this.

Still receiving an error.., 

A single value for column 'app_id' in table 'LTV' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Super User
Posts: 770
Registered: ‎09-16-2018

Re: Calculate measurment Dynamicly based on filters

@eli_p

 

wondering if you got a chance to test my solution?



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Frequent Visitor
Posts: 7
Registered: ‎10-23-2018

Re: Calculate measurment Dynamicly based on filters

@LivioLanzo 
I'm trying this now, will update how it progressed 

Super User
Posts: 866
Registered: ‎06-23-2016

Re: Calculate measurment Dynamicly based on filters

the code I posted is for calculated column (to be added in LTV table), the error suggest that you use it as a measure - is that the case?

Frequent Visitor
Posts: 7
Registered: ‎10-23-2018

Re: Calculate measurment Dynamicly based on filters

@Stachu Sorry again, I changed it to column it worked thanks. 

@LivioLanzo the Data[retention] is not a column, it's a measurement so I tried to remove the calculation and I did not succeed with this option.