Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Table1 |
ID |
1 |
2 |
3 |
4 |
5 |
Table 2 | |
ID | Date |
1 | 2/6/2020 |
2 | 6/8/2020 |
3 | 1/8/2020 |
4 | 30/07/2020 |
5 | 25/07/2020 |
Output |
Id |
1 |
id 1 is present in table2 but not active from last 30 days
Solved! Go to 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
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
@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
@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"
Hi @Anonymous,
Please share a pbix file with some dummy data to test.
Regards,
Xiaoxin Sheng
@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))))
@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"
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |