Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Filter

Hi all   I am trying to filter another table using the formula below. Is there a way to make this work using the filters. I can calculate the sum when the filter is not applied. Cheers   John Sm...
Need Help
  • WinterMist's avatar
    WinterMist
    3 years ago

    Anonymous 

     

    No worries.  I'm learning just like you.

     

    To clarify, you CAN create CC's (Calculated Columns) in the 'Times' table.

    I was simply saying that the result won't look like your screenshot of desired results.

    - Your desired result screenshot presents 5 rows of data on only 3 rows (Grouping By Order No. & Milestone)

    - The data table 'Times' does not group because it's a data table.

     

    Regardless, below is how to create the CC's:

     

    NOTE: When creating multiple CC's in the same table (which use CALCULATE), this creates a Circular Dependency.  To get around this you have to use ALLEXCEPT.

     

    Here is the data view of the 'Times' table along with the 2 added CC's.

     

     

    Hope this is helpful to you.

     

    Nathan

     

WinterMist's avatar
WinterMist
Icon for Impactful Individual rankImpactful Individual
3 years ago

Anonymous 

 

When you say, "I can calculate the sum when the filter is not applied", do you mean that measure 01 below works, but measure 02 does not?

 

01 Total Hours =

CALCULATE( 
   SUM('Times'[Hours])
)  
 
02 John Smith =
CALCULATE(
   SUM('Times'[Hours]),
   'Times'[Name] = "John Smith"
)  
 
- Can you be more specific in how measure 02 is not working?  (I'm not understanding the problem.)
- Is there an error?
- If the problem is undesired results, can you include a screenshot of the result you are getting, and also a screenshot of the expected result (e.g. Excel mock-up)?
 
Regards,
Nathan
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks for your response! 

 

To be more clear, I have two tables: "Times" and "Output".  There is a name, order no. ,order no. milestone and hours column in the Times table. Each Order No. has many milestones and I am trying to sum the hours column in the Times table to find the total hours each user has spent on each order no. milestone 

 

The calculation is to be displayed in a new column of the times table. I can successfully calculate a sum without filters e.g.

 

John Smith = SUM('Times'[Hours])
 
But with filters there is no output (columns are blank) even though there are no error messages displayed. Obviously I expect an output with the summed hours.
 
Order No. left & Times Table right: 
Cheers