Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DDL1976
Frequent Visitor

Help with a DAX Switch (Filter with a Related Table)

Hi there,

 

I have a DAX function that is working, but I want to extend the logic so its Dynamic rather than hard coded..

I have a slicer that selects 1 of the 2 options in the table below 'Report up to'

Then a little piece of DAX to feed the Switch Function.

Select Report To = MIN('DimReport To'[Switch])

 

Here is the DAX containing the SWITCH logic

Report To = 
  SWITCH([Select Report To],
    1, Calculate (sum(FactActualAndPlans[Value]), Filter(FactActualAndPlans, RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30))) ,
    2, SUM(FactActualAndPlans[Value])

 

In the first option of the switch I need the date of 2020,04,30 to reference the date in the first row of table below called ('DimReport To') that can periodically change. (This date could be placed in a different table if that makes it more simple?)

Period End Date		Switch		Type	                  
28 February 2021	1			Finalised Periods Only	  
31 December 2999	2			All Periods	          

 

 

I hope I have explained it clearly enough...

Thanks in advance for the help of the community!!!

1 REPLY 1
amitchandak
Super User
Super User

@DDL1976 , Something like this

 

SWITCH(True()
[Select Report To] =1 && RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30) , Calculate (sum(FactActualAndPlans[Value]), Filter(FactActualAndPlans, RELATED(DimPeriod[Period End Date]) <= DATE(2020,4,30))) ,
[Select Report To] = 2, SUM(FactActualAndPlans[Value])
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.