I"m working on a report to identify if resources have been committed correctly to various projects. The demand hours, commit hours, and constrained hours (gap hours) are on three different rows in the data set and I'm trying to consolidation all of them to 1 row. I created a Y\N flag to identify if the value in the committed column is either 0 or null. the data set looks like this: WorkCategoryValueProject NameIDResourceRoleValueResourceNameIdTaskDetailTier34OrgIdMonthCatHoursDateDemand HrsCommit HrsBlankCommitFlagProject35Electrical Engineer 2 TTS Analyst11AugGap488/1/202000NProject35Electrical Engineer 2 TTS Analyst11Aug 488/1/2020480NProject35Electrical Engineer 2 TTS Analyst11AugCommit 8/1/20200 Y I aggregated the data set, but I'm generating two rows where there should be only one, it appears as follows:WorkCategoryValueProjectNameIdResourceRoleValueResourceNameIdTaskDetailDateBlankCommitFlagDemand HrsCommit HrsProject35Electrical Engineer 2 TTS Analyst8/1/2020N480Project35Electrical Engineer 2 TTS Analyst8/1/2020Y0 I'm trying to find a way to replace the 0 values with null so I can consolidate the aggregated table into 1 row. I'm not sure how to do this and could use some help, ixdutt

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Keeping null values in aggregation table

I"m working on a report to identify if resources have been committed correctly to various projects. The demand hours, commit hours, and constrained hours (gap hours) are on three different rows in the data set and I'm trying to consolidation all of them to 1 row. I created a Y\N flag to identify if the value in the committed column is either 0 or null. the data set looks like this:

WorkCategoryValue	Project NameID	ResourceRoleValue	ResourceNameId	TaskDetail	Tier34OrgId	MonthCat	Hours	Date	Demand Hrs	Commit Hrs	BlankCommitFlag
Project	35	Electrical Engineer 2		TTS Analyst	11	AugGap	48	8/1/2020	0	0	N
Project	35	Electrical Engineer 2		TTS Analyst	11	Aug	48	8/1/2020	48	0	N
Project	35	Electrical Engineer 2		TTS Analyst	11	AugCommit		8/1/2020	0		Y

I aggregated the data set, but I'm generating two rows where there should be only one, it appears as follows:

WorkCategoryValue	ProjectNameId	ResourceRoleValue	ResourceNameId	TaskDetail	Date	BlankCommitFlag	Demand Hrs	Commit Hrs
Project	35	Electrical Engineer 2		TTS Analyst	8/1/2020	N	48	0
Project	35	Electrical Engineer 2		TTS Analyst	8/1/2020	Y	0

I'm trying to find a way to replace the 0 values with null so I can consolidate the aggregated table into 1 row. I'm not sure how to do this and could use some help,

ixdutt

Anonymous

Hi Anonymous

I build the same table as yours to have a test.

Just like mwegener replied before you when you summarize ,you don’t need to add the BlankCommitFlag.

I build Measure to achieve your goal.

'AGG_REsource Request =

VAR _MAXDH =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Demand Hrs]

    )

VAR _MAXGH =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Gap Hours]

    )

VAR _MAXCS =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Commit Hrs]

    )

RETURN

    SUMMARIZE (

        'Table',

        [WorkCategoryValue],

        [Project NameID],

        'Table'[ResourceRoleValue],

        'Table'[ResourceNameId],

        'Table'[TaskDetail],

        'Table'[Date],

        "Demand Hrs", _MAXDH,

        "Gap Hours", _MAXGH,

        "Commit Hrs", _MAXCS

    )

Result:

You can download the pbix file from this link: Keeping null values in aggregation table

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Rico Zhou

6 Replies

amitchandak
Super User
6 years ago
Anonymous , it not coming in one row because of CommitFlag, it has value Y and N. Take Max for that?

Typically Numbers has default aggregation as a sum. That should add up to one row. Or use some aggregation for Demand Hrs ,Commit Hrs
- Anonymous
  Not applicable
  6 years ago
  Amit,
  
  Thanks for your reply, but does the MAX() function apply to Y\N values in a column? I thought the function only applies to numeric values only.
  
  ixdutt
mwegener
Most Valuable Professional
6 years ago
Hi Anonymous,

how you aggregated the data?
- Anonymous
  Not applicable
  6 years ago
  mwegener,
  
  I first added the following column to the source data set to identify null values in the data set (I realize that null values are replaced by 0 values in aggregation tables):
  BlankCommitFlag = IF(ISBLANK([Commit Hours]), "Y", "N"
  
  Then in the Data View, Modelling > Create Table
  
  'AGG_REsource Request = SUMMARIZE('FACT_ResourceRequestList',[WorkCategoryValue],[ProjectNameId],[Tier34OrgId],' etc. to build out the aggregated table.
  
  I hope that helps,
  
  ixdutt
  - mwegener
    Most Valuable Professional
    6 years ago
    Hi Anonymous
    
    do not include the column BlankCommitFlag in your SUMMARIZE

Anonymous

Not applicable

6 years ago

Hi Anonymous

I build the same table as yours to have a test.

Just like mwegener replied before you when you summarize ,you don’t need to add the BlankCommitFlag.

I build Measure to achieve your goal.

'AGG_REsource Request =

VAR _MAXDH =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Demand Hrs]

    )

VAR _MAXGH =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Gap Hours]

    )

VAR _MAXCS =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Project NameID] = MAX ( 'Table'[Project NameID] )

        ),

        'Table'[Commit Hrs]

    )

RETURN

    SUMMARIZE (

        'Table',

        [WorkCategoryValue],

        [Project NameID],

        'Table'[ResourceRoleValue],

        'Table'[ResourceNameId],

        'Table'[TaskDetail],

        'Table'[Date],

        "Demand Hrs", _MAXDH,

        "Gap Hours", _MAXGH,

        "Commit Hrs", _MAXCS

    )

Result:

You can download the pbix file from this link: Keeping null values in aggregation table

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Rico Zhou

Forum Discussion

Keeping null values in aggregation table

6 Replies

Related content

Total Min/Max measure where and keep the value static

OneLake storage keeps growing?

Data Aggregation issue

Datasets keep losing gateways

Combine Rows only keeping unique values