Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Employee Count with Different Versions and Dates

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 DateAggTypeResolved Count
23-FebV15012
23-MarV15074
23-AprV14748
23-MayV14968
23-JunV15014
23-JulV14641
23-AugV15293
23-SepV14752
23-SepV21
23-OctV14451
23-OctV217
23-NovV14113
23-NovV23790
23-DecV14966
23-DecV24764
24-JanV15586
24-JanV25488
24-FebV14766
24-FebV24685
24-MarV14350
24-MarV24350
24-AprV26748
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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!

2 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Anonymous  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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    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!