Forum Discussion

Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
5 years ago
Solved

How to create a unique identifier flag in dataset

Hello Power BI Team,

The data I have presented is all on the same table. I want to create a column that identifies if the record is unique based on the Latest Cteated_Date, Customer Name, Agent Name.

I believe I can use the Earlier Function but I am not sure how to do it. Can you help?

The table below is the results I am trying to achieve

 

Case                  Customer Name         Latest Created_Date        Agent Name            Agent Location             Unique Record

114935437LOKI11/17/2020 17:35Capt AmericaAvengers Tower1
114935438LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935439LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935440LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935441LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935442LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935443LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
114935444LOKI11/17/2020 17:35Capt AmericaAvengers Tower 
110759610THANOS9/8/2020 19:37Apil O'NealNew York1
110759789THANOS9/8/2020 19:39Apil O'NealNew York1
111084676ULTRON9/14/2020 13:38HULKAvengers Tower1
111084966THANOS9/14/2020 13:40HULKAvengers Tower1
111233012THANOS9/15/2020 20:06HULKAvengers Tower1
111233013THANOS9/15/2020 20:06HULKAvengers Tower 
111233014THANOS9/15/2020 20:06HULKAvengers Tower 
111688380THANOS9/23/2020 13:25Peter ParkerAvengers Tower1
111742935THANOS9/23/2020 19:56Peter ParkerAvengers Tower1
111743168THANOS9/23/2020 19:58Peter ParkerAvengers Tower1
111743330THANOS9/23/2020 19:59Peter ParkerAvengers Tower1
111851971THANOS9/25/2020 13:19Dr. StrangeMultiverse1
111851972THANOS9/25/2020 13:19Dr. StrangeMultiverse 
111851973THANOS9/25/2020 13:19Dr. StrangeMultiverse 
111851974THANOS9/25/2020 13:19Dr. StrangeMultiverse 
111851975THANOS9/25/2020 13:19Dr. StrangeMultiverse 
111986026THANOS9/28/2020 21:26Peter ParkerAvengers Tower 
112068538THANOS9/30/2020 15:30FalconAvengers Tower1
112068539THANOS9/30/2020 15:30FalconAvengers Tower 
112068540THANOS9/30/2020 15:30FalconAvengers Tower 
112068687THANOS9/30/2020 15:32FalconAvengers Tower1
112859127THANOS10/12/2020 19:49Janet Van DyneAvengers Tower1
113033489THANOS10/14/2020 21:27Janet Van DyneAvengers Tower1
113143196THANOS10/16/2020 15:38Janet Van DyneAvengers Tower1
113312513THANOS10/20/2020 15:37Wanda VisionAvengers Tower1
113312514THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113312515THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113312516THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113312517THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113312518THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113312519THANOS10/20/2020 15:37Wanda VisionAvengers Tower 
113483912THANOS10/22/2020 16:43Tony StarkAvengers Tower1
113483913THANOS10/22/2020 16:43Tony StarkAvengers Tower 
113483914THANOS10/22/2020 16:43Tony StarkAvengers Tower 
114032879THANOS11/2/2020 15:34Capt MarvelAvengers Tower1
114032880THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114032881THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114032882THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114032883THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114032884THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114032885THANOS11/2/2020 15:34Capt MarvelAvengers Tower 
114212338THANOS11/4/2020 16:49Winnie La PoohDisney1
114414888THANOS11/7/2020 14:53Janet Van DyneAvengers Tower1
114840350THANOS11/16/2020 16:22GrootPlanet Nova 
114840725THANOS11/16/2020 16:24GrootPlanet Nova 
114840726THANOS11/16/2020 16:24GrootPlanet Nova 
114840727THANOS11/16/2020 16:24GrootPlanet Nova 
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi Jorgast 

    Try this.

    Unique record V1 = 
VAR isUnique_ =
    CALCULATE (
        COUNT ( Table1[Case] ),
        ALLEXCEPT (
            Table1,
            Table1[Customer Name],
            Table1[Latest Created_Date],
            Table1[Agent Name]
        )
    ) = 1
RETURN
    IF (
        isUnique_,
        1,
        VAR minCase_ =
            CALCULATE (
                MIN ( Table1[Case] ),
                ALLEXCEPT (
                    Table1,
                    Table1[Customer Name],
                    Table1[Latest Created_Date],
                    Table1[Agent Name]
                )
            )
        RETURN
            IF ( minCase_ = Table1[Case], 1 )
    )

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

     

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    Cheers 

     

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Jorgast 

    Try this.

    Unique record V1 = 
VAR isUnique_ =
    CALCULATE (
        COUNT ( Table1[Case] ),
        ALLEXCEPT (
            Table1,
            Table1[Customer Name],
            Table1[Latest Created_Date],
            Table1[Agent Name]
        )
    ) = 1
RETURN
    IF (
        isUnique_,
        1,
        VAR minCase_ =
            CALCULATE (
                MIN ( Table1[Case] ),
                ALLEXCEPT (
                    Table1,
                    Table1[Customer Name],
                    Table1[Latest Created_Date],
                    Table1[Agent Name]
                )
            )
        RETURN
            IF ( minCase_ = Table1[Case], 1 )
    )

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

     

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    Cheers 

     

     

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Jorgast  Are your desired results in the unique column what you're looking for? I get different results when I calculate, but this is the DAX for new column that will identify unique values based on those three columns: 

     

    Unique = IF(COUNTROWS(FILTER(ALL(CaseTable), CaseTable[Customer Name]= EARLIER(CaseTable[Customer Name]) && CaseTable[Latest Created_Date] = EARLIER(CaseTable[Latest Created_Date]) && CaseTable[Agent Name]= EARLIER(CaseTable[Agent Name])))=1, 1)
    • Jorgast's avatar
      Jorgast
      Icon for Resolver II rankResolver II
      5 years ago

      AllisonKennedy 

       

      Thank you for your response. When I add your code it shows me all the unique values. In the case where Agent name, Case date, agent name are all the same the data shows blank. I need it to show 1 record is unique as well.

       

      i apologize for not being clear on what I am trying to accomplish

       

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Jorgast  That's fine, you're looking for distinct count then. Does this need to be as a calculated column in this case? You can use the DISTINCTCOUNT function in a measure perhaps? 

         

        If you want as a column, try: 

         

        Distinct = IF(COUNTROWS(FILTER(ALL(CaseTable), CaseTable[Case Record]<=EARLIER(CaseTable[Case Record]) && CaseTable[Customer Name]= EARLIER(CaseTable[Customer Name]) && CaseTable[Latest Created_Date] = EARLIER(CaseTable[Latest Created_Date]) && CaseTable[Agent Name]= EARLIER(CaseTable[Agent Name])))=1, 1)

         

        Unique = IF(COUNTROWS(FILTER(ALL(CaseTable), CaseTable[Customer Name]= EARLIER(CaseTable[Customer Name]) && CaseTable[Latest Created_Date] = EARLIER(CaseTable[Latest Created_Date]) && CaseTable[Agent Name]= EARLIER(CaseTable[Agent Name])))=1, 1)