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] The...
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

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.