Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys, I hope you can help me, I need identify if a user change the assignned región comparing last date vs previous date, creating a column with yes or not depending the movement and create other column to have the previous Región in case change it.
For Example
Original Table
Expect Table with adding columns:
If change region compare before month yes or not
if yes getting previous Region
Regards and thank you so much!!
Solved! Go to Solution.
Here are two expressions for your columns.
Change Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
CALCULATE (
MAX ( Regions[Date] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] < __thisdate
)
VAR __prevregion =
CALCULATE (
MAX ( Regions[Region] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] = __prevdate
)
RETURN
IF ( OR ( __thisregion = __prevregion, ISBLANK ( __prevregion ) ), "No", "Yes" )
Previous Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
CALCULATE (
MAX ( Regions[Date] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] < __thisdate
)
VAR __prevregion =
CALCULATE (
MAX ( Regions[Region] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] = __prevdate
)
RETURN
IF ( __thisregion = __prevregion, BLANK (), __prevregion )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here are two expressions for your columns.
Change Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
CALCULATE (
MAX ( Regions[Date] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] < __thisdate
)
VAR __prevregion =
CALCULATE (
MAX ( Regions[Region] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] = __prevdate
)
RETURN
IF ( OR ( __thisregion = __prevregion, ISBLANK ( __prevregion ) ), "No", "Yes" )
Previous Region =
VAR __thisregion = Regions[Region]
VAR __thisdate = Regions[Date]
VAR __prevdate =
CALCULATE (
MAX ( Regions[Date] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] < __thisdate
)
VAR __prevregion =
CALCULATE (
MAX ( Regions[Region] ),
ALLEXCEPT ( Regions, Regions[Name] ),
Regions[Date] = __prevdate
)
RETURN
IF ( __thisregion = __prevregion, BLANK (), __prevregion )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You have right @mahoneypat , the problem was my Column Date had text format, sorry, changing the format the columns works perfect. Thank you very much.
hi @mahoneypat
Im adding the new columns that you shared me, but the result expect is different that show in my original post.
Shared Calculate Result:
Below I Show you the differences with the expect result.
Explain me better for the example, I need to identify when a person change the region comparing with the previous month in each row with yes(Change Region Column) and register the previous region(Previous Region Column)
This is the example, with the original table, the calculate columns you shared me and the expect result for every column
Regards ant thanks to follow up my post.
That's odd. In my pbix with your example data, I see your expected result, so there is something different in our two models. Can you post the adapted expressions you are using with your Table/Column names? Were all of the "Regions" table names replaced with your table name?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |