topic Re: FILTER AND COLUMN[HELP] in DAX Commands and Tips

FILTER AND COLUMN[HELP]

FrankWoody — Thu, 23 Jun 2022 01:15:26 GMT

I have this base table that updates weekly the Last update column indicates the last update of the projects.
Project_Id is a unique key for each project
base_data is the project start date
data project_end is the deadline for the project to be completed.

Project_Id	NOTES	Data_base	Data_ProjectEend	LAST UPDATE
0a887f68	Traveled to NY	24/10/2022	25/03/2023	03/01/2022
0a8881ac	Traveled to BO	25/10/2022	26/03/2023	03/01/2022
0a8882b0	Traveled to NI	26/10/2022	27/03/2023	03/01/2022
0a8883a0	Traveled to EM	27/10/2022	28/03/2023	03/01/2022
0a88849a	Traveled to CL	28/10/2022	29/03/2023	03/01/2022
0a88858a	Traveled to PL	29/10/2022	30/03/2023	03/01/2022
0a88868e	Traveled to SS	30/10/2022	31/03/2023	03/01/2022
0a88877e	Traveled to WE	31/10/2022	01/04/2023	03/01/2022
0a888b0c	Traveled to AF	01/11/2022	02/04/2023	03/01/2022
0a888c10	Traveled to OO	02/11/2022	03/04/2023	03/01/2022
0a887f68	Traveled to NY	24/10/2022	25/03/2023	04/03/2022
0a8881ac	Traveled to BO	25/10/2022	01/04/2023	04/03/2022
0a8882b0	Traveled to NI	26/10/2022	30/03/2023	04/03/2022
0a8883a0	Traveled to EM	27/10/2022	28/06/2023	04/03/2022
0a88849a	Traveled to CL	28/10/2022	29/10/2023	04/03/2022
0a88858a	Traveled to PL	29/10/2022	30/03/2023	04/03/2022
0a88868e	Traveled to SS	30/10/2022	31/03/2023	04/03/2022
0a88877e	Traveled to WE	31/10/2022	01/04/2023	04/03/2022
0a888b0c	Traveled to AF	01/11/2022	02/05/2023	04/03/2022
0a888c10	Traveled to OO	02/11/2022	03/04/2023	04/03/2022

The thing is I have some smart guys who like to change this project_end date so when that happens I would like bi to show up the project_end data and count how many times they were changed and show a summary sheet like this, the status would show how many times the project_end was changed this could help to create a conditional formatting green "OK" and red"It would indicate a change and I would see how many times it changed".

tamerj1

@SpartaBI

@Vijay_A_Verma

Project_Id	NOTES	Data_base	Data_ProjectEend	STATUS
0a887f68	Traveled to NY	24/10/2022	25/03/2023	0
0a8881ac	Traveled to BO	25/10/2022	01/04/2023	1
0a8882b0	Traveled to NI	26/10/2022	30/03/2023	1
0a8883a0	Traveled to EM	27/10/2022	28/06/2023	1
0a88849a	Traveled to CL	28/10/2022	29/10/2023	1
0a88858a	Traveled to PL	29/10/2022	30/03/2023	0
0a88868e	Traveled to SS	30/10/2022	31/03/2023	0
0a88877e	Traveled to WE	31/10/2022	01/04/2023	0
0a888b0c	Traveled to AF	01/11/2022	02/05/2023	1
0a888c10	Traveled to OO	02/11/2022	03/04/2023	0

Re: FILTER AND COLUMN[HELP]

tamerj1 — Thu, 23 Jun 2022 03:30:12 GMT

Hi FrankWoody

not sure if I correctly understand but this should be simple. Just place the ID in the table visual then add other columns as measures. All of them can just SELECTEDVALUE except the end date should be MAX. the new column is just COUNTROWS ( Table ) -1

Re: FILTER AND COLUMN[HELP]

FrankWoody — Thu, 23 Jun 2022 11:29:02 GMT

How can I Right it in DAX. ?

Re: FILTER AND COLUMN[HELP]

tamerj1 — Thu, 23 Jun 2022 11:43:38 GMT

FrankWoody
Like this? https://www.dropbox.com/t/jCppbj0iG8WHnqyU

Or you need to create a new calculated table?

Re: FILTER AND COLUMN[HELP]

FrankWoody — Fri, 24 Jun 2022 02:11:06 GMT

My idea is to pull the information straight from the MS planner, my activity is to check if the managers are not changing the Data_ProjectEend, this way I can follow how many times they changed the final date my problem is that the BI does not store the history because every time it updates directly from the data source it overwrites the Data_ProjectEend and I ended up losing the previous date if it changes.

Re: FILTER AND COLUMN[HELP]

tamerj1 — Fri, 24 Jun 2022 02:16:43 GMT

Hi FrankWoody

add a new measure to the table

Number of changes =

COUNTROWS ( TableName ) - 1

Re: FILTER AND COLUMN[HELP]

FrankWoody — Fri, 24 Jun 2022 02:48:35 GMT

Yes that worked!!
My problem is my database, for example, in the original there are only 10 lines that would update its status. but for testing purposes I doubled it to 20 lines and changed some dates to simulate the changes.
would I be able to store the information in the BI, to apply this comparison without them being overwritten.
For example this week I would automatically update the BI directly from the web, and next week when I would update the Bi I would store the data from this table to compare with the next update. Did you understand? [

Re: FILTER AND COLUMN[HELP]

tamerj1 — Fri, 24 Jun 2022 03:12:58 GMT

Hi FrankWoody

https://www.dropbox.com/t/41Hy38PGp5cLi5CK

In this case you can modify your measure to count the number of changes up to the selected channge date. So if you want to see wht was the status of your data at any previous week you just select that week and see how many changes where there.

Number of Changes = COUNTROWS ( CALCULATETABLE ( project_end, project_end[LAST UPDATE] <= MAX ( project_end[LAST UPDATE] ) ) ) - 1