topic Re: DAX Filter in DAX Commands and Tips

DAX Filter

Anonymous — Fri, 18 Aug 2023 12:32:32 GMT

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 Smith =
CALCULATE(

  SUM('Times'[Hours]),

    'Times'[Name] = "John Smith")

Re: DAX Filter

WinterMist — Fri, 18 Aug 2023 12:59:36 GMT

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

Re: DAX Filter

Anonymous — Mon, 21 Aug 2023 13:34:12 GMT

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

Re: DAX Filter

WinterMist — Mon, 21 Aug 2023 21:37:10 GMT

Anonymous

Thanks for this information.

1) Hopefully I am not misunderstanding you, but it sounds like "Output" is not actually a data table in the model like "Times", but simply a table visual. As a result, I have created a model with only 1 table for "Times". (If this is incorrect, please specify the columns and corresponding sample data for the "Output" data table.)

2) To sum the hours column in the Times table to get the total hours each user spent on each milestone, I created 2 measures & dragged them onto a table visual.

As you can see, this table visual result set matches your desired output screenshot from Excel.

3) The main thing that confuses me is that you specify: "the calculation is to be displayed in a new COLUMN of the times table." Calculated Columns operate in the RC (Row Context). As a result, if you do this, you will not get the output you are looking for because your output summarizes (or groups by) the Order No. & Milestone. But the 'Times' table does not do this because of the presence of the [Name] column. For this reason, to get the output that matches your screenshot, I used 2 measures within a table visual.

Hopefully this is helpful to you.

Regards,

Nathan

Re: DAX Filter

Anonymous — Tue, 22 Aug 2023 11:22:13 GMT

WinterMist

Sorry I am still very new to Power BI but I appreciate you taking the time. The entire Times table receives input to SQL Server from power apps whilst the Output table requires input for only the following fields: Order No., Milestone, Description, SO Type, MS Type, Project Leader, Planned Hrs, Design Due Date, Production Milestone, Completed, Reason For Late, Hrs Variance.

The following fields in the output table were initially calculated using excel but are to be calculated using Power BI: Days Late, Status, Open Late, Req'd Month, Req'd Year, Past(1)/Present(0), Comp Wk No, Comp Yr, User e.g. "John Smith", Total Hrs, Variance, % Var, -MONTH, Current.

I have managed all calculations besides the user fields in the output table (e.g. Brian Wiles column). This is what I meant by a new column to display the sum output. This way a user will be able to view the output tables as it was on excel. Is it possible to achieve this? I don't see why not since it can be done on excel and SQL?

Re: DAX Filter

WinterMist — Tue, 22 Aug 2023 20:07:44 GMT

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

Re: DAX Filter

Anonymous — Wed, 23 Aug 2023 08:29:51 GMT

WinterMist

I have implemented your code which has no errors but still fails to produce the desired code (still producing blanks despite valid data). I think the issue may not even be the code since you achieve the desired result yourself? I have attached the Times table (top) and Output table (bottom). Cheers

Re: DAX Filter

Anonymous — Thu, 24 Aug 2023 15:49:30 GMT

WinterMist

I have now solved the problem using the last code you provided and by creating a new calculated column with milestone and Order Number concatenated to form a single ID.

Thanks for your help