Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX (calculate duplicates dynamically)

Hi,  

I have a question considering DAX functions.

 

I have two columns : 1) customerID and 2) time stamp

 

From these two columns, I calculated "all customers", "first time visitors" and "repeaters" for each month.

So each month, the repeating customers start again at 0.

 

I want to know if there is a DAX function to calculate the repating customers dynamically.

So if I set the slicer bar in between months, that it automatically shows me the repeaters for the given time frame.

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    You can create measure like this, the measure is repeater


    measure
    =sumx(summarize(sales,sales[customer_id], "_Cnt",count(sales[customer_id])),if([_cnt]>1,1,0))

     

    This one is new customer


    measure
    =sumx(summarize(sales,sales[customer_id], "_Cnt",count(sales[customer_id])),if([_cnt]=1,1,0))

     

    all in the given duration. If you plot by customer then you will the customer name

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      Hi, thanks for your prompt reply.

      I want to create a bar chart like the one on the right in the attached picture to see all the first time customers and repeaters for each day. At the moment I see all first time / repeaters for one month. 

      Your measure basically worked, but I cannot show the result in a bar chart. Also the first time they come to the store within the chosen time, should be counted as 1.

       

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        We can use the following measures to meet your requirement:

         

         

        first time =
VAR time =
    MONTH ( MAX ( 'Table'[date] ) )
VAR year =
    YEAR ( MAX ( 'Table'[date] ) )
VAR t =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( 'Table'[date] ) = time
                && YEAR ( 'Table'[date] ) = year
        ),
        'Table'[uuid],
        "First", MIN ( 'Table'[date] )
    )
RETURN
CALCULATE ( COUNTROWS ( FILTER ( t, [First] IN DISTINCT ( 'Table'[date] ) ) ) )

         

         

         

        repeater =
VAR time =
    MONTH ( MAX ( 'Table'[date] ) )
VAR year =
    YEAR ( MAX ( 'Table'[date] ) )
VAR t =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( 'Table'[date] ) = time
                && YEAR ( 'Table'[date] ) = year
        ),
        'Table'[uuid],
        "First", MIN ( 'Table'[date] )
    )
RETURN
    DISTINCTCOUNT ( 'Table'[uuid] )
        - CALCULATE ( COUNTROWS ( FILTER ( t, [First] IN DISTINCT ( 'Table'[date] ) ) ) )

         

         

        And we can get the result like this,

         

         

         

        If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

         

        BTW, pbix as attached.


        Best regards,