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

[InventoryMan]

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:

Region	Total Sites	Expected Assets	Pending Sites
North	3	75	1
South	2	50	1
East	1	25	1
West	5	125	3

Sample Data:

Locations[id]	Locations[Region]	Locations[SiteCount]	Locations[Status]
1	North	25
2	North	25	Complete
3	North	25	Complete
4	South	25
5	South	25	Complete
6	East	25
7	West	25
8	West	25
9	West	25
10	West	25
11	West	25	Complete

Assets[Location_id]	Assets[InventoryDate]
1	7/4/1976
1	7/4/1976
1	7/4/1976
1	7/4/1976
1	7/4/1976
2	1/1/2023
2	1/1/2023
2	1/1/2023
4	7/4/1976
4	7/4/1976
4	7/4/1976
5	7/4/1976
5	7/4/1976
5	7/4/1976
5	7/4/1976
5	7/4/1976
5	7/4/1976
6	7/4/1976
6	7/4/1976
6	7/4/1976
7	7/4/1976
7	7/4/1976
7	7/4/1976
7	7/4/1976
8	7/4/1976
8	7/4/1976
8	7/4/1976
8	7/4/1976
9	7/4/1976
9	7/4/1976
9	7/4/1976
9	7/4/1976
10	1/1/2023
10	1/1/2023
10	1/1/2023
10	1/1/2023
11	1/1/2023
11	1/1/2023
11	1/1/2023
11	1/1/2023

[v-yangliu-msft · Accepted solution]

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:

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

[InventoryMan]

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

[InventoryMan]

Desired end result, and sample datat tables added.

[amitchandak]

@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/

[InventoryMan]

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