Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Jorgast
Resolver II
Resolver II

How to create a unique identifier flag in dataset

‎12-18-2020 12:33 PM

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 
Labels:
Message 1 of 5
1,156 Views
0
1 ACCEPTED SOLUTION
AlB
Super User
Super User

‎12-21-2020 11:06 AM

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 

 

SU18_powerbi_badge

 

View solution in original post

Message 5 of 5
1,045 Views
4 REPLIES 4
AlB
Super User
Super User

‎12-21-2020 11:06 AM

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 

 

SU18_powerbi_badge

 

Message 5 of 5
1,046 Views
AllisonKennedy
Super User
Super User

‎12-18-2020 01:25 PM

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Message 2 of 5
1,125 Views
0
Jorgast
Resolver II
Resolver II
In response to AllisonKennedy

‎12-21-2020 10:26 AM

@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

 

 

Message 3 of 5
1,051 Views
0
AllisonKennedy
Super User
Super User
In response to Jorgast

‎12-21-2020 12:42 PM

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Message 4 of 5
1,030 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All