Filtering Disconnected Tables based on selection

[nssidhu74]

I am using Direct Query

1. I have a view that uses the following Query

Select Distinct(SourceCountry),
( Select MAX(Data_date) from FactInventory FI Where FI.SourceCountry = I.SourceCountry) AS MaxDate,
      ( Select Sum(Volume) from FactInventory FI Where FI.SourceCountry = I.SourceCountry and FI.Data_Date = ( Select MAX(Data_date) from FactInventory FII Where FII.SourceCountry = I.SourceCountry) ) AS SumVolume
       From FactInventory I

I Display the output of above Query into Table(Direct output from Table View)

2. Then I create FilteredFactInventory(using NEW Table) with the following Formula

FilteredInventory =

VAR SelectedCountry = SELECTEDVALUE(vwInventorySummary[SourceCountry],"Argentina")

VAR SelectedDate = SELECTEDVALUE(vwInventorySummary[MaxDate],TODAY())

VAR Data = FILTER(FactInventory,FactInventory[Data_Date]=Date(2019,12,19) && FactInventory[SourceCountry]="UK")

VAR Data = FILTER(FactInventory,FactInventory[Data_Date]=SelectedDate && FactInventory[SourceCountry]=SelectedCountry)

RETURN

//FILTER(FactInventory,FactInventory[Data_Date]=[SelectedDate] && FactInventory[SourceCountry]=[SelectedCountry])

//FILTER(FactInventory,FactInventory[SourceCountry]=[SelectedCountry])

Data

3. Next i Create Measure "SelectedCountry"

MeasureSelectedCountry =

//VAR SelectedCountry = SELECTEDVALUE(vwInventorySummary[SourceCountry],"Argentina")

VAR SelectedCountry=IF(SELECTEDVALUE(vwInventorySummary[SourceCountry])=BLANK(),"UK",SELECTEDVALUE(vwInventorySummary[SourceCountry]))

RETURN

SelectedCountry

When I change the Selection in the Table above I do not get proper Filter #2 FilteredFact Table.

This is what i get when i change the selection

As can be seen above, irrespective of what i select in the top table my botton table, which is based on "FilteredInventory" Table from STEP #2 does not filter the data properly or alwayd displays data for UK

What could be wrong here ?

I Tried Setting up Relation between the Table using Model but i can only do that for Source Country, Date is Repeated in the above table for diiferent companies and it sets up Many-To-Many Relation, but even after setting that up it still does not work

Also another puzzling thing is that whenever i change the selection it fires the Query to DB, which takes around 7-10 Seconds to come back.

[amitchandak]

I think you are looking for category sum and max

Please refer

https://community.powerbi.com/t5/Desktop/Sum-of-values-by-each-category/td-p/134279

https://community.powerbi.com/t5/Desktop/SUM-value-by-category/td-p/507457

You can also explore quick measure to achieve the same.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601