Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX function to check if data is consistent

Hi there!   I have a huge table with employee data from two data sources (SharePoint, SAP). My data looks like this: ID Name CC Entry date Leaving date Div BU Quitting reason Quitting ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    This is the code for a calculated column

     

    Any Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            CALCULATETABLE (
                SELECTCOLUMNS (
                    'Employee Data',
                    "@Name", [Name], "@CC", [CC], "@EntryDate", [Entry date], "@LeavingDate", [Leaving date],
                    "@@Div", [Div], "@BU", [BU], "@QuittingReason", [Quitting reason], "@QuittingType", [Quitting type],
                    "@WorkingHours", [Work hours], "@FTE", [FTE], "@Location", [Location], "@position", [Position]
                ),
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[Name] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 
Not sure what exactly you're looking for but following is one way of doing that.

Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            FILTER (
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[DataSource] ),
                'Employee Data'[ID] = SELECTEDVALUE ( 'Employee Data'[ID] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

I get the following error trying your solution: 

A circular dependency was detected: Employee_Data[Differences?].

The output should be a new column in the data source table as you did it. 

It would be useful to have an additional column, which is empty by default, but in case of difference it would show the name of the column that differs.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    This is the code for a calculated column

     

    Any Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            CALCULATETABLE (
                SELECTCOLUMNS (
                    'Employee Data',
                    "@Name", [Name], "@CC", [CC], "@EntryDate", [Entry date], "@LeavingDate", [Leaving date],
                    "@@Div", [Div], "@BU", [BU], "@QuittingReason", [Quitting reason], "@QuittingType", [Quitting type],
                    "@WorkingHours", [Work hours], "@FTE", [FTE], "@Location", [Location], "@position", [Position]
                ),
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[Name] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    This is a measure code not a calculated column.