Forum Discussion
DAX Filter
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
Impactful IndividualAnonymous
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
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?