Forum Discussion
Using DAX Measure for Categories against SSAS Direct Connection
I have seen several posts about creating Categories using DAX but I'm having difficulty using any of them against my SSAS connection.
I'm trying to create aging buckets and do a count of distinct values falling into each bucket. So far, I've been able to create my aging buckets and view them in a table that contains each value. The data is similar to the following:
| Code | End Date | Aging Bucket (DAX) |
| 1 | 6/29/2020 | Past Due |
| 2 | 10/29/2021 | Beyond 4 months |
| 3 | 5/31/2020 | Past Due |
| 4 | 4/29/2021 | One month |
I'd like to be able to display:
| Aging Buckets | Count of Code |
| Past Due | 2 |
| One Month | 1 |
| Beyond 4 months | 1 |
The problem seems to be putting the Buckets in the rows of the matrix. In my situation using SSAS, I'm haven't found a way to create a table and use it. (I'm not sure why other than it might have something to do with using a direct connection to the SSAS Tabular model.)
My Categories were created with the following:
Aging Buckets =
VAR Latest =
MAXX ( 'Dimension1', 'Dimension1'[End Date] )
VAR Bucket =
CALCULATE (
IF (
Latest,
IF (
HASONEVALUE ('Dimension1'[End Date]),
SWITCH (
TRUE,
[NO of days] <= 0, "Past Due",
[NO of days] > 0
&& [NO of days] <= 30, "One Month",
[NO of days] > 30
&& [NO of days] <= 60, " Two Months",
[NO of days] > 60
&& [NO of days] <= 90, " Three Month",
[NO of days] > 90
&& [NO of days] <= 120, " Four Month",
"More Than 4 Months"
)
)
)
)
RETURN
Bucket
Days past due:
NO of days =
VAR TD = TODAY()
VAR ED = SELECTEDVALUE('Dimension1'[End Date])
RETURN
IF (TD < ED, DATEDIFF(TD,ED,DAY), DATEDIFF(ED,TD,DAY) * -1)
Count of Codes:
Aging Count = CALCULATE(COUNTAX('Dimension1','Dimension1'[Code]))
How do I use it in a matrix to group the aging counts by each bucket?
Thanks.
Hi msatterwhite ,
Yes,you cant put measure in rows,could you use "No of days" instead?
Such as below:
If you need the column "No of days" showing in matrix ,you could hide it by tricks:Go to column headers>switch "word wrap":off
You could check my updated .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
8 Replies
- amitchandakSuper User
msatterwhite , actually to do measure bucketing you need to use an independent date table. As you are using SSAS, you might have go few things at SSAS only
I have explained bucketing here :https://youtu.be/CuczXPj0N-k
or refer these
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization- msatterwhiteFrequent Visitor
Thank you for your response. I've been trying to use the technique from your video and the links you provided but I'm unable to put my Buckets measure in the Rows area. I think my direct-connection to SSAS prevents me from being able to use the Buckets measure the way I want to.
Have you tried your approach when using a "Connect-live" connection to SSAS? I'm curious to know if it works.
Thanks.
Please see my reply to v-kelly-msft above, which goes into more detail.
- v-kelly-msftCommunity Support
Hi msatterwhite ,
Create a measure as below:
measure = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Aging Buckets]=MAX('Table'[Aging Buckets])))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- msatterwhiteFrequent Visitor
Thank you for your response. I think I'm running into difficulty doing this while using a live-connection to SSAS. No matter what I try, I'm unable to put the Aging Buckets in the rows. It won't allow me to move it there.
In your visual, you were able to put Aging Buckets in the Rows:But, the best I can get is for both measures to land in the Values.
I used the same measures you used.
Have you tried to create use the measures when using a direct-connection to SSAS?
A potential clue is shown when I use your .pbix file and try to make an additional connection to SSAS. I'm only able to connect using Import, which my users don't want to do.- v-kelly-msftCommunity Support
Hi msatterwhite ,
Yes,you cant put measure in rows,could you use "No of days" instead?
Such as below:
If you need the column "No of days" showing in matrix ,you could hide it by tricks:Go to column headers>switch "word wrap":off
You could check my updated .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!