Forum Discussion

InventoryMan's avatar
InventoryMan
Frequent Visitor
2 years ago
Solved

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

    • InventoryMan's avatar
      InventoryMan
      Frequent Visitor
      2 years ago

      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.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      InventoryMan
      Frequent Visitor
      2 years ago

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