Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
6 years ago
Solved

DAX calulation Composite model

Hi all,

 

I'm still confuse on how to use many DAX functions in regards of limitation because of using Direct Query. 

There are some DAX function which I want to use such "SELECTEDVALUE", "MAX", and of course "CALCULATE". But all this turned down because it is said not allowed because DirecQuery models.

 

For example:

That error coming because I'm trying to create a calculated column using a measure that was created by SELECTEDVALUE function to get a date from a slicer. 

 

I've read the DAX function that supported in Direct query :

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016

SELECTEDVALUE not in that list, but MAXX is, but that also throw me an error. 

 

But then I realize, in that document, it does says "Supported in measure and query formulas only" 

My curiosity is whether this limitation cannot overcome by composite model ? And is switching the storage mode of the table I would like to add the calculated column (which now has error) to Mixed mode meaning it is composite model ? Because not very clear how to turn on that composite after the option : "Allow unrestricted measure for DirectQuery" gone.

 

The other thing is, since important DAX like CALCULATE or simply MAX or SELECTEDVALUE cannot be in calculated column, what is the work around if I do need that ?

 

For example, I want to create Aging report, usually we put calculated column in our transaction, comparing to our aging bucket (Aging group) like below :

AgeGroup = CALCULATE(
        VALUES(AgingGroup[Age Group]),
        FILTER(AgingGroup,
                FactInternetSales[CustomerAge] >= AgingGroup[Min] &&
                FactInternetSales[CustomerAge] < AgingGroup[Max]
        )
)

 

How to achieved that when CALCULATE function is not allowed for DirectQuery ?

 

Thanks in advance,

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    admin_xlsior ,

     

    For the first question, the limitation of dax in direct query will not be affected by specific model(e.g.: composite model). The basic difference is differences between in-memory and DirectQuery mode. Please refer to doc: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016#differences-between-in-memory-and-directquery-mode

     

    For the second question, as you mentioned, SELECTEDVALUE() is not supported in direct query mode, VALUES() and MAXX() are supported in measure and query formulas only, not calculate columns. So the workaround is to change calculate column with measure in power bi or create the calculate field in SSAS and connect to AS model in live model.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      6 years ago

      Hi, 

      thanks for the clarification. But now I'm confuse, how to change DAX that originaly for Calculated column to measures ?

       

      For example if I have this table :

      ItemInvoiceDateAmount
      P-10026717-Mar-16200
      P-1003611-Jan-10500
      P-1003621-Jan-10100
      P-10026617-Mar-16400
      P-10026517-Mar-16800
      P-10026118-Mar-16900

       

      And I create a measure to get the input date from date slicer : 

      As of date = VALUES('Date'[DATE_])

       

      In normal practice I should create caluclated column, so each row has the date difference. Should be something like this :

      Age = [As of date] - 'table'[InvoiceDate]

       

      Then while I have the aging bucket like below:

      AgegroupMinMax
      <20 days020
      21-100 days21100
      >100 days1019999

       

      So, with the infromation of [Age] per row from the 1st calculated column, the next calculated column will be something like :

      AgeGroup = CALCULATE(
              VALUES(AgingBucket[Age Group]),
              FILTER(AgingBucket,
                      Table[Age] >= AgingBucket[Min] &&
                      Table[Age] < AgingBucket[Max]
              )
      )

       

      By this usualy we can create a matrix something like :

      Item<20 days21-100days>100 days
      Aamountamountamount
      Bamountamountamount
      Camountamountamount

       

      So, how to turn both calculated column to measures ? and does it still make sense to produce that kind of aging report ?

       

      Thanks,

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        admin_xlsior ,

         

        If calculate column doesn't work, I would suggest you to create model in SSAS and then connect to SSAS with power bi.

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.