cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

DAX query to extract data from SSAS model filtering a specific value in a field

I am connecting to a cube in SSAS which has multiple tables linked in the Model already. I want to extract the data with specific criteria. I have tried the below DAX

 

Evaluate(filter('Table',[Field]="Value"))

 

This has resulted in the extraction of all the fields in that particular table "Table" but does not show me the full data linked with different tables.

 

I would like to extract all the data in the model, filtering only data if the filter criteria meet condition( specific value in a particular field in a particular table "Table" show me all the data linked in the model.

 

Appreciate your help!

 

LionelR_0-1599188148010.png

 

thanks

Lionel

7 REPLIES 7
Super User IV
Super User IV

@LionelR Can you just connect to the cube, suck in everything and then go back and filter that table in your query for that table? Use the default navigator and select everything instead of using advanced to enter your own DAX.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Hi Greg,

 

Thanks for your response! I have tried this already and successfully created a report in Power Bi desktop, but having issues publishing due to gateway error.

 

LionelR_0-1599189322356.jpeg

 

 

hence, I want to import the data with specific filter criteria and be able to extract data using DAX spanning across different linked tables in the model 

 

thanks for your help!

HI @LionelR,

>>Thanks for your response! I have tried this already and successfully created a report in Power Bi desktop, but having issues publishing due to gateway error.

I guess it would relate to power bi security and privacy.
When you working on desktop side, it has enough permission to process these functions; when you publish to power bi service side, obviously you do not have full permissions to process these queries host on power bi service. Some of the functions will be blocked due to security reasons. (this also apply to other types of script and codes such as r script, python that host in your report)

Power BI Security#data-storage-security 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@LionelR - Odd, I know it's possible to import data from cubes: https://radacad.com/add-or-edit-columns-when-importing-data-from-analysis-services-into-power-bi

 

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Hi Greg,

 

Thanks for your response!

 

I am working out how to restrict data using DAX while importing, to a specific value in a field in a model with multiple tables.

 

Do you know if I could use a different syntax? the above formula restricts and only shows data from a single table and other data from linked tables are lost.

 

Thanks

 

@LionelR - No, but you could create a new query for the other table and then use the table you imported in order to either do a Merge or filter the new table coming in, effectively accomplishing what you want, I *think*. If I understand correctly, you want to import this table within only the rows you want and then have the other related tables also have only the rows that correspond with the rows in this first table that you are importing. Right?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Super User IV
Super User IV

@LionelR , I think this one will create a new table and that is not joined to any other table. As SASS live connection will not allow any data model changes. You may have to prefer to do all such changes in the schema in SASS



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors