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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
InventoryMan
Frequent Visitor

Advice on creating new summary table, based on filtering data from 2 other tables.

I have been trying to replicate a dashboard, previously created via MySQL. I am unable, or aware if what I am trying to replicate can be done.
 
The dashboard is "grouped by" Locations[Region]
There is a relationship created between 2 tables; Assets & Locations locations[id] = assets[location_id]
 
In some cases I am trying to FILTER the results, based on data in 1 or both tables. I cannot figure out the correct FILTER syntax, or if I'm even going about it the correct way. For the "Pending Sites" column I want to filter on the red text. I believe through trial and error I was able to get 1 filter, from the Locations table to work. I have been stuck adding 2 filters, and they are from 2 different tables.
 
SUMMARIZECOLUMNS(Locations[Region],
"Total Sites", COUNT('Locations'[Id]),
"Expected Assets", SUM('Locations'[SiteCount]),
"Pending Sites", COUNT('Locations'[Id]),
/*Assets[InventoryDate] = DATE(1976, 7, 4) AND Locations[Status] <> 'complete'*/
)
Desired Result:
RegionTotal SitesExpected AssetsPending Sites
North3751
South2501
East1251
West51253

 

Sample Data:

Locations[id]Locations[Region]Locations[SiteCount]Locations[Status]
1North25 
2North25Complete
3North25Complete
4South25 
5South25Complete
6East25 
7West25 
8West25 
9West25 
10West25 
11West25Complete

 

Assets[Location_id]Assets[InventoryDate]
17/4/1976
17/4/1976
17/4/1976
17/4/1976
17/4/1976
21/1/2023
21/1/2023
21/1/2023
47/4/1976
47/4/1976
47/4/1976
57/4/1976
57/4/1976
57/4/1976
57/4/1976
57/4/1976
57/4/1976
67/4/1976
67/4/1976
67/4/1976
77/4/1976
77/4/1976
77/4/1976
77/4/1976
87/4/1976
87/4/1976
87/4/1976
87/4/1976
97/4/1976
97/4/1976
97/4/1976
97/4/1976
101/1/2023
101/1/2023
101/1/2023
101/1/2023
111/1/2023
111/1/2023
111/1/2023
111/1/2023
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi  @InventoryMan ,

 

Here are the steps you can follow:

1. Create calculated table.

Table =
var _column=
SELECTCOLUMNS(
    FILTER(ALL(Assets),
    'Assets'[InventoryDate]=DATE(1976,7,4)),"Column_ID",'Assets'[Location_id])
var _table1=
SUMMARIZE(
    'Locations','Locations'[Region],
    "Total Sites",COUNT('Locations'[id]),
    "Expected Assets",SUM('Locations'[SiteCount]),
    "Pending Sites",COUNTX(FILTER(ALL(Locations),'Locations'[id] in _column&&'Locations'[Status]<>"Complete"&&'Locations'[Region]=EARLIER('Locations'[Region])),[Region]))
return
_table1

2. Result:

vyangliumsft_0-1697681026212.png

 

Best Regards,

Liu Yang

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
Anonymous
Not applicable

Hi  @InventoryMan ,

 

Here are the steps you can follow:

1. Create calculated table.

Table =
var _column=
SELECTCOLUMNS(
    FILTER(ALL(Assets),
    'Assets'[InventoryDate]=DATE(1976,7,4)),"Column_ID",'Assets'[Location_id])
var _table1=
SUMMARIZE(
    'Locations','Locations'[Region],
    "Total Sites",COUNT('Locations'[id]),
    "Expected Assets",SUM('Locations'[SiteCount]),
    "Pending Sites",COUNTX(FILTER(ALL(Locations),'Locations'[id] in _column&&'Locations'[Status]<>"Complete"&&'Locations'[Region]=EARLIER('Locations'[Region])),[Region]))
return
_table1

2. Result:

vyangliumsft_0-1697681026212.png

 

Best Regards,

Liu Yang

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

Thank you for your assistance on this! I have now been learning about the provided syntax.

InventoryMan
Frequent Visitor

Desired end result, and sample datat tables added.

amitchandak
Super User
Super User

@InventoryMan , Try like

 

SUMMARIZECOLUMNS(Locations[Region],
"Total Sites", COUNT('Locations'[Id]),
"Expected Assets", SUM('Locations'[SiteCount]),
"Pending Sites", COUNT('Locations'[Id]),
Filter(Assets, Assets[InventoryDate] = DATE(1976, 7, 4) )
Filter(Locations, Locations[Status] <> 'complete')
)

 

 

refer

https://www.sqlbi.com/articles/introducing-summarizecolumns/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Thank you, but I still receive an error for the filter. I will add some sample data, and perhaps there is another way to accomplish what I am looking for.

 

InventoryMan_0-1697550429712.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors