Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
arnomics
Helper I
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.

 

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.

1 ACCEPTED SOLUTION

Hi @arnomics ,

 

You can use viual filter:

vjianbolimsft_0-1685523949541.png

Final output:

vjianbolimsft_1-1685523963776.png

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.

View solution in original post

5 REPLIES 5
v-jianboli-msft
Community Support
Community Support

Hi @arnomics ,

 

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

vjianbolimsft_0-1685511578382.png

vjianbolimsft_1-1685512020337.png

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:

vjianbolimsft_2-1685512037949.png

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.

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. 

 

 

Hi @arnomics ,

 

You can use viual filter:

vjianbolimsft_0-1685523949541.png

Final output:

vjianbolimsft_1-1685523963776.png

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.

amitchandak
Super User
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

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. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.