cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcoDekker
Helper I
Helper I

How to mimic a sql IN query using powerbi on a SSAS cube

Hi all.

i am trying to mimic some kind of set filtering. In sql is is very easy, but i am using a SSAS cube.
I would like to mimic this in powerbi


In the below query, i first determine which customers have an aggregated sales amount of over 1000. Then just for those customers, i need to list their name and country

select CustomerID, CustomerName, CustomerCountry
from CustomerTable
where customerID in ( select customerID
                                    from SalesTable
                                    group by CustomerID
                                    having sum(SalesAmount) > 1000

                                   )

How to achieve this?

4 REPLIES 4
MarcoDekker
Helper I
Helper I

Anybody with an alternative aproach?

Nobody? Simply not possible?

Tahreem24
Super User
Super User

@MarcoDekker  Create a new table using SUMMERIZE

New Table = SUMMERIZE(Table, Table[CustomerID],Table[CustomerName],Table[CustomerCountry],"Amount" SUM(SalesAmount)>1000))

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis

Hi @Tahreem24 . Thanks for this suggestion.

We use a SSAS cube provided by a supplier. I can use Dax allright when creating measures in a PBI report, but no columss or tables. So i am unable to use the summerize feature.

regards, Marco

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors