Forum Discussion
Filtering Disconnected Tables based on selection
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"
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
1 Reply
- amitchandakSuper User
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601