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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

i want to show the list of 1st table id's who's is not present in the 2nd table from the last 30days

Table1
 
ID
1
2
3
4
5

 

Table 2 
  
IDDate
12/6/2020
26/8/2020
31/8/2020
430/07/2020
525/07/2020

 

Output
 
Id
1

 

id 1 is present in table2 but not active from last 30 days


1 ACCEPTED SOLUTION

Hi @Anonymous,

So you mean you want to add a calculated column to table1 to mark records who not active in the last 30 days? If this is a case, you can try to use the following calculate column formats to mark all the inactive IDs.

IsActived =
VAR _maxDate =
    MAXX ( ALLSELECTED ( Table2 ), [Date] )
VAR _actived =
    CALCULATETABLE (
        VALUES ( Table2[ID] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Date]
                >= DATE ( YEAR ( _maxDate ), MONTH ( _maxDate ), DAY ( _maxDate ) - 30 )
        )
    )
RETURN
    IF ( Table1[ID] IN _actived, "Actived", "Inactived" )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

You can try to use the following measure formula if it meets your requirement:

Measure =
VAR _maxDate =
    MAXX ( ALLSELECTED ( Table2 ), [Date] )
VAR _list =
    CALCULATETABLE (
        VALUES ( Table2[ID] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Date]
                >= DATE ( YEAR ( _maxDate ), MONTH ( _maxDate ), DAY ( _maxDate ) - 30 )
        )
    )
RETURN
    CONCATENATEX ( EXCEPT ( VALUES ( Tabl1[ID] ), _list ), [ID], "," )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft 30 days logic is works but i need to see table1 id's in one column, whichever id's not present in the second table from the last 30 days. no need to concanate the id's.. In my report i want to show the count of id in card visual. if i get those id then i will just count of that id's.

basically the requirment is whichever table1 id's not present in the table2 id column from last 30days, we can consider as inactive accounts.

Hi @Anonymous,

So you mean you want to add a calculated column to table1 to mark records who not active in the last 30 days? If this is a case, you can try to use the following calculate column formats to mark all the inactive IDs.

IsActived =
VAR _maxDate =
    MAXX ( ALLSELECTED ( Table2 ), [Date] )
VAR _actived =
    CALCULATETABLE (
        VALUES ( Table2[ID] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            [Date]
                >= DATE ( YEAR ( _maxDate ), MONTH ( _maxDate ), DAY ( _maxDate ) - 30 )
        )
    )
RETURN
    IF ( Table1[ID] IN _actived, "Actived", "Inactived" )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft thanks it's working, but when i update 7 instead of 30 ,getting below error:-
"an argument of function 'date' has the wrong data type or the result is too large or too small"

error.png

 

 

Hi @Anonymous,

Please share a pbix file with some dummy data to test.

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous m Try

 

Table
Table  =except(all(Table1), selectcolumns(filter(Table2, Table2[Date]>=today()-30),"ID",Table2[ID))

 

Measure

measure = calculate(countrows(Table1), filter(Table1,Table[ID] in except(all(Table1), selectcolumns(filter(Table2, Table2[Date]>=today()-30),"ID",Table2[ID))))

Anonymous
Not applicable

@amitchandak in both table other columns are different , they are not same.
getting the error "each table argument of "except" must have the same number of columns"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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