Using DAX Measure for Categories against SSAS Direct Connection

[msatterwhite]

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.

[v-kelly-msft]

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,
Kelly

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

[msatterwhite]

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-msft · Accepted solution]

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,
Kelly

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

[msatterwhite]

Unfortunately, I'm still unable to use your solution.  In your solution, you have a column for NO of days that isn't calculated.  I have to calculate my NO of days as a measure built by subtracting my [End Date] column from today.  That's preventing me from being able to use NO of days as a row or column in a matrix.  

The only columns I get from my SSAS model are Category and End Date.  Are you able to find a way to get a matrix to display the same way if everything in it is a measure?

[v-kelly-msft]

Hi @msatterwhite ,

How do you like the solution as below:

As only measure is available,if you dont have  a key column to summarize the [_Aging Buckets],then it would show similarly as above.

The measure is as below:

measure = 
var tab=SUMMARIZE('Table','Table'[Category],"aging",[_Aging Buckets])
Return
COUNTX(tab,[aging])

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[msatterwhite]

Thanks Kelly.  That's the best I was able to do as well.  I was hoping to make it a summarized view of the data but it sounds like that's not possible with each of those fields being measures.  

I appreciate your continued help.  I needed that confirmation.

[amitchandak]

@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

[msatterwhite]

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.