Employee Count with Different Versions and Dates

[bcobrien1977]

Hi,

I am trying to normalize some outside data and there are basically duplicate values for most of 2024 and some of 2023 so I need to be able to use the Agg Type of V2 if present and greater than 1 and else use v1. 

This is a sample of the data but it doesn't fit perfectly.  I feel like I just can't get the dax right.  Does anyone have any ideas?    The Date table is connected to the report date of the data table  Thanks

Report Date	AggType	Resolved Count
23-Feb	V1	5012
23-Mar	V1	5074
23-Apr	V1	4748
23-May	V1	4968
23-Jun	V1	5014
23-Jul	V1	4641
23-Aug	V1	5293
23-Sep	V1	4752
23-Sep	V2	1
23-Oct	V1	4451
23-Oct	V2	17
23-Nov	V1	4113
23-Nov	V2	3790
23-Dec	V1	4966
23-Dec	V2	4764
24-Jan	V1	5586
24-Jan	V2	5488
24-Feb	V1	4766
24-Feb	V2	4685
24-Mar	V1	4350
24-Mar	V2	4350
24-Apr	V2	6748

[v-huijiey-msft · Accepted solution]

Hi @bcobrien1977 ,

Thanks for the reply from @smpa01 .

In my understanding, your requirement is to return the Resolved Count of V2 if there is more than one value for the same year and month, otherwise return the Resolved Count of V1. am I understanding correctly?

Although your date column only has year and month, powerbi may recognize different days, for example, February automatically recognizes the 23rd and 24th.

Therefore, you need to create a new Year Month column:

Year Month = FORMAT('Table'[Report Date],"yyyy-mm")

Create a sort column so that the Year Month column is sorted by this column:

Year Month Sort = YEAR('Table'[Report Date])*12+MONTH('Table'[Report Date])

Create a Measure that counts the number of each Year Month column:

Count = 
CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Year Month]))

Creates a Measure and returns the Resolved Count of V2 for the same date if count>1, otherwise returns the Resolved Count of V1:

Measure 2 = IF(
    [Count] > 1,
    CALCULATE( MAX ( 'Table'[Resolved Count] ), ALLEXCEPT('Table','Table'[Year Month]), 'Table'[AggType] = "V2"  ),
    CALCULATE ( MAX ( 'Table'[Resolved Count] ), ALLEXCEPT('Table','Table'[Year Month]) , 'Table'[AggType] = "V1")
)

The final page result is shown below:

The pbix file is attached.

If you have other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[smpa01]

@bcobrien1977  can you try this

Measure =
VAR baseTbl =
    SUMMARIZE (
        ALL ( _tbl ),
        _tbl[Report Date],
        _tbl[AggType],
        _tbl[Resolved Count]
    )
VAR f1 =
    FILTER ( baseTbl, [Report Date] = MAX ( _tbl[Report Date] ) )
VAR _count =
    COUNTROWS ( f1 )
VAR factV2 =
    CALCULATE ( MAX ( _tbl[Resolved Count] ), FILTER ( f1, _tbl[AggType] = "V2" ) )
VAR factV1 =
    CALCULATE ( MAX ( _tbl[Resolved Count] ), FILTER ( f1, _tbl[AggType] = "V1" ) )
VAR ternary =
    IF ( _count > 1 && factV2 > 1, factV2, factV1 )
RETURN
    ternary