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
eli_p
Regular Visitor

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

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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
            )
        )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

13 REPLIES 13
eli_p
Regular Visitor

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 ) ))).

Stachu
Community Champion
Community Champion

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

instead of

'LTV'[app_id]



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@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.

Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@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.


eli_p
Regular Visitor

@Stachu
So the solution worked for me to calculate the ARPU_D1
But when I'm trying to do the same for APRU_D2, ARPU_D3 etc.. which is basically uses the same formula just increases the date diff between  +2, +3 I'm receiving an error for circular dependency. 
"A circular dependency was detected: LTV[ARPU_D2], LTV[ARPU_D1_COL], LTV[ARPU_D2]."

I think it somehow related to the MAX ( 'LTV'[Arpdau_all_col])

Do you how to solve this issue? 

Thanks,


Stachu
Community Champion
Community Champion

I cannot replicate the issue, I can add new columns without a problem - I adjusted the syntax to match your naming convention, do these work properly?

Column = 
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_id] = App
                    && 'LTV'[country] = Country
                    && 'LTV'[install_date] = CurrentDate + 1
            )
        )

and +2 days

Column 2 = 
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_id] = App
                    && 'LTV'[country] = Country
                    && 'LTV'[install_date] = CurrentDate + 2
            )
        )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi @StachuThis is "ARPU_D2"

ARPU_D2 = 
VAR v_App = 'LTV'[app_id]
VAR v_Country = 'LTV'[country]
VAR v_CurrentDate = 'LTV'[install_date]
RETURN
'LTV'[ret_d2]
* CALCULATE (
MAX ( 'LTV'[Arpdau_all_col] ),
FILTER (
'LTV',
'LTV'[app_id] = v_App
&& 'LTV'[country] = v_Country
&& 'LTV'[install_date] = v_CurrentDate + 2
)
)

This is "ARPU_D1"

ARPU_D1 = 
VAR v_App = 'LTV'[app]
VAR v_Country = 'LTV'[country]
VAR v_CurrentDate = 'LTV'[install_date]

RETURN
     'LTV'[ret_d1]
        * CALCULATE (
            MAX ( 'LTV'[Arpdau_all_col] ),
            FILTER (
                'LTV',
                'LTV'[app] = v_App
                    && 'LTV'[country] = v_Country
                    && 'LTV'[install_date] = v_CurrentDate + 1
            )
        )


As you see I'm using a different column for "retention" but it should not affect the circular dependency issue.
The dependency wis between : LTV[ARPU_D2], LTV[ARPU_D1], LTV[ARPU_D2].

All the variables are columns, only "retention" is a measurement ("ret_d1", "ret_d2") 





 

 

Stachu
Community Champion
Community Champion

ret_d1 and ret_d2 are measures? what's their syntax? 

if they're based on the LTV table then for sure they would cause the circular reference - they are used in columns, which are part of LTV, which is the table they are based on



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@eli_p

 

wondering if you got a chance to test my solution?

 


 


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


Proud to be a Datanaut!  

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

LivioLanzo
Solution Sage
Solution Sage

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Stachu
Community Champion
Community Champion

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
            )
        )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.