DAX Dynamic filter in Power Pivot 2016

Question

Hi all, I have a data model which I use in Excel 2016 Power Pivot. Here is a simplified version of the fact table and dimension table:

I am trying to calculate a calculated measure that sums the Amt from the Group field. For example, if I am filtered on subgroup 1, I would like the measure to show me '10' which is the sum of Group 'A'.

I have tried the following:

   Measure =   
   CALCULATE(  
             SUM(FactTable[Amt]),  
             FILTER(DimesionTable,DimensionTable[Subgroup]=MAX(DimensionTable[Subgroup]))  
   )  

But I get an error saying Max can only be applied to numeric values. How can I create this measure in Power Pivot 2016?

Answer 1

@Wads ,

I make a simple sample, then I use the formula =CALCULATE(SUM(Table2[Amount]),FILTER(Table1,Table1[Group]=LOOKUPVALUE(Table1[Group],Table1[SubGroup1],1))) to create an explicit measue.

If you want to filter on other SubGroup, you may enter other SubGroup number.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.