Forum Discussion

arnomics's avatar
arnomics
Helper I
3 years ago
Solved

Filtering on a CALCULATETABLE function

Hi all,

 

I'm trying my luck again as my previous post might've been complicated. I'll try to simplify this post. 

I have created the below Variable - to produce & isolate a list of Customers that are based in City = Hobart.

 

VAR HOBART =
CALCULATETABLE (
SELECTCOLUMNS (
VALUES (Customer),
"City", Customer[City],
"Code", Customer[Customer Code]
),
Customer[City] = "Hobart"
)

 

I have then created another Variable for my Product Table that Filters for Red or Black Color.

 

VAR REDORBLACK =

FILTER(
VALUES('Product'[Color]),
OR(
'Product'[Color] = "Red",
'Product'[Color] = "Black"
)
)

 

I now want to produce an output where I am only looking for Customers that are based in Hobart that have Products Red or Black. Basically, VAR HOBART to Filter VAR REDORBLACK. The output should look something like.

 

CityCustomer CodeRed or Black Count
Hobart13
Hobart210
Hobart91

 

I am using a Published dataset with a lot of rows for Customers table & as a result, I'm trying to create a Variable for Hobart to avoid errors with processsing millions of Rows.

 

Hope someone can provide some suggestions.

 

Thanks.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    arnomics , Simple create one measure or two measures

     

    calculate(countrows(Table), filter(Product, Product[Color] in {"Red", "Black"} ))

     

    if you create a new table, that table should join to your fact and then use countrows(table) measure

    • arnomics's avatar
      arnomics
      Helper I
      3 years ago

      Thanks for your reply amitchandak 

       

      The main issue is that I'm using a Published Dataset and adding New Tables, Columns etc. is greyed out. It stops me from doing lot of things that would make my life easier. 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi arnomics ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Table = 
var _a = FILTER('Customer',[City]="Hobart")
return SUMMARIZE(_a,[City],Customer[Customer Code],"Red or Black Count",CALCULATE(COUNT('Product'[Product Code]),FILTER('Product',[Color] in {"Red","Black"})))

    Output:

    Best Regards,

    Jianbo Li

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

    • arnomics's avatar
      arnomics
      Helper I
      3 years ago

      Thanks for your reply v-jianboli-msft 

      Since I am using a Published dataset I cannot create a Table in the Data Model. But instead I was hoping to create a Measure that would allow me to drag & drop Columns from the table different tables with the Measure that I created.