Forum Discussion

MrPowerBI's avatar
MrPowerBI
Frequent Visitor
8 years ago

DAX measure to dynamically filter date dimension

We have a datawarehouse solution set up with Power BI and SSAS Tabular which, among other things, include a fact table for our current employee record (HR). This fact table is extracted from the HR s...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi MrPowerBI,

Please try the following formula and check if it works fine.

Sum of FTEs :=
VAR SelectedDate =
    LASTNONBLANK ( Time[Date], 1 )
RETURN
    CALCULATE ( SUM ( HR[EmploymentPercentage] ), Time[Date] = SelectedDate )


Best Regards,
Angelia

MrPowerBI's avatar
MrPowerBI
Frequent Visitor
8 years ago

Hi, v-huizhn-msft

 

Thank you so much for the reply.

 

Unfortunately that doesn't do the trick. The SelectedDate variable just returns the last date from the date dimension, without checking if there is data for that date in the fact table. For instance, if I select Year=2017, SelectedDate is set to 2017-12-31 while the last data in my fact table is 2017-10-10. So the measure returns a blank.

 

Any other suggestions?

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi MrPowerBI，

    Do you mind share your sample table or .pbix file for further analysis? You can send it by private message if your data is confidential.

    Best Regards,
    Angelia