cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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.

 City Customer Code Red or Black Count Hobart 1 3 Hobart 2 10 Hobart 9 1

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.

1 ACCEPTED SOLUTION
Community Support

Hi @arnomics ,

You can use viual filter:

Final 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.

5 REPLIES 5
Community Support

Hi @arnomics ,

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

``````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.

Helper I

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.

Community Support

Hi @arnomics ,

You can use viual filter:

Final 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.

Super User

@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

Helper I

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.