Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic Filter in a Calculated Column - No Relationship

Good Morning All,

 

I have two tables, one is a direct export (Table 1), and another is a 'dummy' table (Table 2) with one column (Week Endings) consisting of week ending dates for a few years time.  Table 1 has two date columns I'm interested in, BuiltDate and TeardownDate.  I've added two calculated columns to Table 2 the give me a count of tickets Built and Dismantled in the corresponding week ending row.  This creates a visual that shows Built and Dissasembled by week.

 

The DAX I used for the Built Columns is:

 

Built = CALCULATE(COUNTA(Table1[Build Week]),FILTER(ALL(Table1),Table1[Build Week]=[Week Endings]))
With a similar column for Dismantled.
 
It works great, however the issue becomes that Table1 has a multitude of Job #'s (column = number) I need to be able to filter at the page level.  So, I created a measure,  Job = DISTINCT('Table1'[Number]) and then used the following to try and pull the page filter into the equation:
 
BuiltJobTest = CALCULATE(sum(Table1[Hold]),FILTER(Table1, Table1[Job Number]= [Job] && Table1[Build Week]='Week Endings'[Week Endings]))
 
It doesnt work.  It does however work if i hard key the job number in lieu of [Job] however this would take forever to make a set of columns for each job #.
 
Any ideas?
 
 
Need Help

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

    I still have a little confused about your scenario.

    To understand it better, could you share the sample data and your desired output so that we could help further on it?

    Best  Regards,

    Cherry