cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sowmiya Member
Member

Need Simple DAX in place of Complex DAX

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 datacomm1.png

I need resultant as,comm2.png

 

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 followscomm3.png

Visual I need is
comm4.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Need Simple DAX in place of Complex DAX

@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
                )
            )
        )image.png

     

 

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

 

Regards,

Owen

 



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

Proud to be a Datanaut!




3 REPLIES 3
OwenAuger Super Contributor
Super Contributor

Re: Need Simple DAX in place of Complex DAX

@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
                )
            )
        )image.png

     

 

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

 

Regards,

Owen

 



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

Proud to be a Datanaut!




sreenathv Member
Member

Re: Need Simple DAX in place of Complex DAX

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 idsell id
a1
b1
c1
d1
e1
a1
b1
b1

 

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 idCustomerSalesIDCount
a2
b3
c1
d1
e1

 

 

 

Sowmiya Member
Member

Re: Need Simple DAX in place of Complex DAX

Hi @OwenAuger,
Thanks, It works

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 36 members 1,037 guests
Please welcome our newest community members: