Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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