Keeping null values in aggregation table

[Anonymous]

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

[v-rzhou-msft · Accepted solution]

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

[mwegener]

Hi @Anonymous,

how you aggregated the data?

[Anonymous]

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]

Hi @Anonymous 

do not include the column BlankCommitFlag in your SUMMARIZE

[amitchandak]

@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]

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