Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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
Solved! Go to Solution.
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:
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
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 |
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:
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |