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.
Desired output based on the table below: depending on the Month you select in a filter/slicer, identify only the changes (Adds/Removals of the Identifiers) from the Selected Month vs Previous Month. For example, when selecting February in the filter/slicer, I want to show Identifier "E" (i.e. an Add, because "E" isn't present in January), and also "A" and "C" as the Removals (since they are present in January but not in February). Similarly, when I select March in a filter/slicer, I'd like to show "F" and "G" as the Adds (i.e. not present in February), and also "D" and "E" as the Removals (present in February, but not in March). Is there a way to accomplish this specifically with DAX? I'm able to get the results with anti joins in Power Query, but every time I do this monthly analysis I have to create 2 new filtered tables (filtered on the 2 months I need to analyze the changes on) and I don't want to do it this way. I want to keep 1 single 'append-all table' with all the months' data, and be able to identify the adds/removals based on the 2 months I select. I'm sort of looking for a 'dynamic filtering' option combined with anti joins within DAX.
Solved! Go to Solution.
pls try this
Column 2 =
VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])
return if('Table'[Date2]=max('Table'[Date2]),BLANK(),if(ISBLANK(_last),"removals"))
Proud to be a Super User!
maybe you can create a column
COLUMN =
VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),-1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])
return if(ISBLANK(_last),"Add")
and add that column to filter.
pls see the attachment below
Proud to be a Super User!
Hi!
Thanks for the reply. This works great to identify the Adds. But how can I also show the Removals in the same formula and Column? See screenshot. By selecting February, I would like the COLUMN to also show the Removals, and maybe label "B" and "D" as "no change"?
Thank you.
try this
Proud to be a Super User!
Thank you. This is very helpful. For the Removals, I'm assuming it is not possible to show them in the same column ((by altering the existing COLUMN formula) as the Adds/No changes, since for example Identifier E is either an Add (Feb vs Jan) or a Removal (March vsFeb). In this case, could you please help me "reverse" the Adds formula you provided above, so that it can work the opposite way and identify the Removals? That way I can display 2 tables, one for Adds and one for Removals.
Thank you!
pls try this
Column 2 =
VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])
return if('Table'[Date2]=max('Table'[Date2]),BLANK(),if(ISBLANK(_last),"removals"))
Proud to be a Super User!
Great. This works. Thank you!
you are welcome
Proud to be a Super User!
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 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |