Forum Discussion

msatterwhite's avatar
msatterwhite
Frequent Visitor
5 years ago
Solved

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:

 

CodeEnd DateAging Bucket (DAX)
16/29/2020Past Due
210/29/2021Beyond 4 months
35/31/2020Past Due
44/29/2021One month

 

I'd like to be able to display:

Aging BucketsCount of Code
Past Due2
One Month1
Beyond 4 months1

 

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.

8 Replies

    • msatterwhite's avatar
      msatterwhite
      Frequent 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.

    • msatterwhite's avatar
      msatterwhite
      Frequent 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.