Need Simple DAX in place of Complex DAX

[Sowmiya]

Hi Community,
I need a Simplest form of DAX for DAX which I have created more complicately

In my situation I need to find how many times particular customer id had sales.

This is my actual sales data

I need resultant as,

Thus customer a purchased 2 times

         customer b purchased 3 times
         customer c, customer d, customer e purchase 1 time
I have created a DAX as 
DAX 1: 

Cust 2 = CALCULATE(DISTINCTCOUNT(Sheet1[cus id]),FILTER(Sheet1,COUNT(Sheet1[sell id])=2))
DAX 3:

Cust 3 = CALCULATE(DISTINCTCOUNT(Sheet1[cus id]),FILTER(Sheet1,COUNT(Sheet1[sell id])=3))
DAX 4:

Cust count_1_Sum =

            SUMX (

                       SUMMARIZE(Sheet1,

                       Sheet1[cus id],

                      "test",[Cust 1]

                      ),

                     [Cust 1]

                     )
DAX 5:

Cust count_2_Sum =

                      SUMX (

                            SUMMARIZE(Sheet1,

                            Sheet1[cus id],

                          "test",[Cust 2]

                               ),

                          [Cust 2]

                            )

DAX 6:

Cust count_3_Sum =

                       SUMX (

                            SUMMARIZE(Sheet1,

                            Sheet1[cus id],

                           "test",[Cust 3]

                                ),

                           [Cust 3]

                              )

DAX 7:

Customer Retention =

IF(SELECTEDVALUE('Query1'[Cust Retention])="Count=1",[Cust count_1_Sum],IF(SELECTEDVALUE('Query1'[Cust Retention])="Count=2",[Cust count_2_Sum],IF(SELECTEDVALUE('Query1'[Cust Retention])="Count=3",[Cust count_3_Sum])))

here Query 1 table is manually created table as follows

Visual I need is

In my case I am having 137831 rows. When I run Performance Analyser in Desktop I am getting 4 mins to refresh the DAX and it runs fine. But when I run in Power BI service or link i am getting visuals has exceeded available resources.
So Please anybody help me to resolve this and reduce the complexity of DAX.

This are the errors I am getting when loading the visual in Power BI Link 

Couldn't load the data for this visual
Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.
Http Status Code: 400
Activity ID: 0a21ef2d-b947-45ed-966e-e68555855cb5
Correlation ID: 3c6527ea-3b35-d006-9bfa-d4146412b11c
Request ID: 00ccdd2a-a120-b6bd-914b-690ae52eed82
Time: Mon Jun 24 2019 17:19:30 GMT+0530 (India Standard Time)
Version: 13.0.9880.150
Cluster URI: https://wabi-india-central-a-primary-api.analysis.windows.net/
Activity ID: 0a21ef2d-b947-45ed-966e-e68555855cb5
Request ID: 00ccdd2a-a120-b6bd-914b-690ae52eed82
Time: Mon Jun 24 2019 17:19:30 GMT+0530 (India Standard Time)
Version: 13.0.9880.150
Cluster URI: https://wabi-india-central-a-primary-api.analysis.windows.net/

Thanks,
Sowmiya

[Anonymous]

Hi,

I didn't go through your DAX code but based on your input and output requirements, you could achieve it using the following DAX codes.

I assume that you have the following table named "SalesData".

cus id	sell id
a	1
b	1
c	1
d	1
e	1
a	1
b	1
b	1

Step1: Add a calculated table using the following code.

CustomerSalesIDCount = ALL(SalesData[cus id])

Step 2: Add a calculated column using the following code.

CustomerSalesIDCount = COUNTROWS(FILTER(SalesData,SalesData[cus id]=CustomerSalesIDCount[cus id]))

You will get the following result.

cus id	CustomerSalesIDCount
a	2
b	3
c	1
d	1
e	1

[OwenAuger · Accepted solution]

@Sowmiya 

I would suggest a dynamic segmentation measure in this situation.
(Discussed on DAX Patterns here: https://www.daxpatterns.com/dynamic-segmentation/ )

I have attached a model with dummy data (DAX calculated tables) and a sample measure.

To do this:

1. Create a "segment" table, which in this case includes all possible values of "count of sell id".
   In my example I called this table NumSales and gave it two columns: Index & Cust Retention (matching your naming)
2. Create a measure like this:
   

   Customer Count by Num Sales = 
   VAR SelectedIndex =
       SELECTEDVALUE ( NumSales[Index] )
   RETURN
       IF (
           NOT ISBLANK ( SelectedIndex ),
           COUNTROWS (
               FILTER (
                   VALUES ( Sales[cus id] ),
                   CALCULATE ( SUM ( Sales[sell id] ) ) = SelectedIndex
               )
           )
       )

Hopefully this illustrates the structure and you can tweak as required.

Regards,

Owen

[Sowmiya]

Hi @OwenAuger,
Thanks, It works