Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
liselotte
Frequent Visitor

Flag duplicates in var table DAX

I have table ValueTable. I create a var table of ValueTable filtered by a date in Slicer, then I flag the duplicates of IDs with this query:

Flag =
var _table = ADDCOLUMNS(SUMMARIZE(
FILTER('fact_TimeTable',
('fact_ValueTable'[Date].[Date]=MAX('DateSlicer'[SelectedDate])) &&
('fact_ValueTable'[IsActive]=1)
)
, [ID]
, [Value]
)
, "IDx", [ID])
return
IF(CALCULATE(COUNTROWS(_table), ALLEXCEPT(_table, [IDx]))>1,TRUE(),FALSE())

 But I get an error of Cannot find name ID. Does anyone know why? Thank you in advance.

1 ACCEPTED SOLUTION
AmiraBedh
Resident Rockstar
Resident Rockstar

You're trying to flag rows where the ID appears more than once on the same date and when IsActive is 1, based on a selected date from DateSlicer.

Try the following :

 

Flag =
VAR SelectedDate = MAX('DateSlicer'[SelectedDate])
VAR IsActiveFilter = 1
VAR _table =
    FILTER(
        ALL('fact_ValueTable'), -- Consider using ALL to remove filters if necessary
        'fact_ValueTable'[Date] = SelectedDate &&
        'fact_ValueTable'[IsActive] = IsActiveFilter
    )
VAR _summarizedTable =
    SUMMARIZE(
        _table,
        'fact_ValueTable'[ID],
        "Count", COUNT('fact_ValueTable'[ID]) -- This adds a count of each ID
    )
RETURN
    IF(
        LOOKUPVALUE(
            _summarizedTable[Count],
            _summarizedTable[ID], EARLIER('fact_ValueTable'[ID])
        ) > 1,
        TRUE(),
        FALSE()
    )

 

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

3 REPLIES 3
AmiraBedh
Resident Rockstar
Resident Rockstar

You're trying to flag rows where the ID appears more than once on the same date and when IsActive is 1, based on a selected date from DateSlicer.

Try the following :

 

Flag =
VAR SelectedDate = MAX('DateSlicer'[SelectedDate])
VAR IsActiveFilter = 1
VAR _table =
    FILTER(
        ALL('fact_ValueTable'), -- Consider using ALL to remove filters if necessary
        'fact_ValueTable'[Date] = SelectedDate &&
        'fact_ValueTable'[IsActive] = IsActiveFilter
    )
VAR _summarizedTable =
    SUMMARIZE(
        _table,
        'fact_ValueTable'[ID],
        "Count", COUNT('fact_ValueTable'[ID]) -- This adds a count of each ID
    )
RETURN
    IF(
        LOOKUPVALUE(
            _summarizedTable[Count],
            _summarizedTable[ID], EARLIER('fact_ValueTable'[ID])
        ) > 1,
        TRUE(),
        FALSE()
    )

 

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Thank you a lot! It works like a charm!

Glad to help 😄


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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