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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
julienl
Frequent Visitor

DAX - Calculate table filtered on other unrelated table

Dear All, 

 

I am trying to filter my task calendar table by creating copy of it using calculateTable. 

 

My Calendar table contains task, start date and End Date + more stuff ...

 

I would like to write : 

 

test = CALCULATETABLE(Calendar; 
FILTER(Calendar; Calendar[Start] < CALCULATE(LASTDATE(Dates[Date])));
FILTER(Calendar; Calendar[End] > CALCULATE(FIRSTDATE(Dates[Date]))))

 

I plan to slice my date table to the period I am interested, to get in fine related tasks. 

 

My Dates table isn't related to Calendar because I would need 2 relationships and that is forbidden (which is perfectly understandable). 

I've enclosed the lastdate() and firstdate() inside a calculate() statement to force computation, but it does not seems to have effect. 

I've tried other things but none of it gave the expected result. 

 

Can someone point me to the right direction. 

 

Best regards, 

 

Julien

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @julienl,

I plan to slice my date table to the period I am interested, to get in fine related tasks. 

As a calculate table is computed during the database processing(like a data refresh) and then stored in the model, so it cannot be affected by user selections on the report.

 

In this scenario, I would suggest you use the formula below to create a measure first, then show it on the Table visual with the Calendar[Start], Calendar[End], and other task columns from your "Calendar" table. Then you should be able to use Dates[Date] column as a Slicer to slice the date table to the period you're interested, to get in fine related tasks.Smiley Happy

IsSelected =
IF (
    MAX ( Calendar[Start] ) > MAX ( Dates[Date] )
        && MAX ( Calendar[End] ) > MIN ( Dates[Date] ),
    1
)

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

Hi @julienl,

I plan to slice my date table to the period I am interested, to get in fine related tasks. 

As a calculate table is computed during the database processing(like a data refresh) and then stored in the model, so it cannot be affected by user selections on the report.

 

In this scenario, I would suggest you use the formula below to create a measure first, then show it on the Table visual with the Calendar[Start], Calendar[End], and other task columns from your "Calendar" table. Then you should be able to use Dates[Date] column as a Slicer to slice the date table to the period you're interested, to get in fine related tasks.Smiley Happy

IsSelected =
IF (
    MAX ( Calendar[Start] ) > MAX ( Dates[Date] )
        && MAX ( Calendar[End] ) > MIN ( Dates[Date] ),
    1
)

 

Regards

Thanks v-ljerr-msft!

 

I tought that the table was dynamically computed at every user input, but I does not. 

The measure indeed do the trick. 

I hopped this could be used as a page filter, but apparently it cannot. Anyway, I can now create measures to show what I need. 

 

Thank you for your help!!

 

Julien

It is not working on other data only works with dates why you put ok?


 


 

mattbrice
Solution Sage
Solution Sage

I am not 100% sure i understand what you want - but you can try:

 

 

test = CALCULATETABLE(Calendar; 
FILTER(Calendar; Calendar[Start] > MIN(Dates[Date]) && Calendar[End] < MAX(Dates[Date])))

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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