Forum Discussion
Column flag with DAX
Hello,
I am trying to create a simple flag column using DAX and I am having trouble getting the result that I want. Here is the scenario:
In my source dataset I have initiativeID's that are related to a specific week, and a flag for records in the current week. I would like to add a flag for records that are in the previous week. I've been trying to work this out in DAX with no success. I feel like what I have should work but for some reason it doesnt. My thought process was to look at each ID, and if it is exactly 1 less than the ID of the current week, then flag those records as 1. Otherwise flag as 0. If I pull out just the part where I am subtracting 1 and put that into a separate card, I get the correct value of 10071. Both the ID and previous week flag are currently set to whole number types. Below is a screenshot that highlights exactly which records I want to be flagged as 1.
isPreviousWeek =
IF (
InitiativeOverview_Dimension[initiativeid]
= CALCULATE (
DISTINCT ( InitiativeOverview_Dimension[initiativeid] ),
InitiativeOverview_Dimension[isCurrentWeek] = 1
) - 1,
1,
0
)
Any ideas?
Thanks!
6 Replies
Refer this file. I created a calendar table and created week wise rank. Your last week max rank -1. Calculated this week vs last week too
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Some variables and the use of MAX might be the solution you're looking for.
Something to this effect:
isPreviousWeek = VAR previous_id = CALCULATE( MAX(InitiativeOverview_Dimension[initiativeid]), ALL(InitiativeOverview_Dimension[initiativeid])) - 1 RETURN IF ( SELECTEDVALUE(InitiativeOverview_Dimension[initiativeid]) = previous_id, 1, 0 )Anonymous ,
In case the file did not help you let me know
