Forum Discussion

AndriyK's avatar
AndriyK
Helper II
4 years ago
Solved

Question about IsAvailableforMDX property on a Model

Hello,

 

we are using Power BI with live connection to SSAS tabular.

 

i'm trying to reduce the size of the SSAS tabular model and read few blog posts about IsAvailableforMDX property, the main one: https://blog.crossjoin.co.uk/2018/07/02/isavailableinmdx-ssas-tabular/

i've used Tabular Editor and modified few attributes in Customer table and set IsAvailableforMDX to false and the size of the table descreased as expected. 

But looks like we lost the ability to use the modified attributes in the matrix visual in Power BI.

i've tried to reprocess full the table and database and it didn't help.

when i changed the  IsAvailableforMDX to true and reprocessed, the matrix worked as expected.

does the Power BI matrix visual depend on IsAvailableforMDX propetty the same way as pivot tables in Excel? 

Thank you,

Andriy 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi AndriyK ,

    What you think is correct.This setting will impact Excel connections to the Power BI Analysis Service Tabular model.  Excel relies on MDX to generate query against the DataSet.  If this is set to False, excel users will not be able to select the column in a pivot.   It is the equivalent to hiding a column, but it will also remove the search index hence the size reduction.  It is recommended to use for Measure columns (i.e. things you will aggregate by; not group by).

    @marcorusso explain this in the video round the 40 min mark.  Optimizing Power BI model size and memory by using DAX Studio metrics and VertiPaq Analyzer by Marco...

     

     

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


    Best Regards

    Lucien

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi AndriyK ,

    What you think is correct.This setting will impact Excel connections to the Power BI Analysis Service Tabular model.  Excel relies on MDX to generate query against the DataSet.  If this is set to False, excel users will not be able to select the column in a pivot.   It is the equivalent to hiding a column, but it will also remove the search index hence the size reduction.  It is recommended to use for Measure columns (i.e. things you will aggregate by; not group by).

    @marcorusso explain this in the video round the 40 min mark.  Optimizing Power BI model size and memory by using DAX Studio metrics and VertiPaq Analyzer by Marco...

     

     

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


    Best Regards

    Lucien

    • AndriyK's avatar
      AndriyK
      Helper II
      4 years ago

      hi v-luwang-msft ,

      thanks for confirmation! i will check out the video  
      i really like marcorusso  and AlbertoFerrari books, blog posts and videos.

      somehow, i was under impression that Power BI natively uses DAX and doesn't use MDX and that MDX is used by Excel only. And it is safe to turn off IsAvailableforMDX if the model is accessed by Power BI only. 

      But looks like MDX is used in Matrix visual in Power BI as well.
      thanks again,

      Andriy