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.
Dear community friends, I'm slowing catching up with basic DAX stuff, and not surprisingly I came across a doubt again.
Thanks @MFelix for resolving question in my last post, and this one is closely related to that.
link to last post: https://community.powerbi.com/t5/Desktop/Dax-help-needed-for-latest-8-weeks-total/m-p/190323#M83799
This is my data set,
Table 1
-------------------------------------------------------------------------------------
Stream | Product | Week Ending Date | 8 weeks ago Date | Flag Count
-------------------------------------------------------------------------------------
Red | A | 10/03/2017 | 13/01/2017 | 0
Red | A | 17/03/2017 | 20/01/2017 | 1
Red | A | 24/03/2017 | 27/01/2017 | 1
Red | A | 31/03/2017 | 3/02/2017 | 0
Red | A | 7/04/2017 | 10/02/2017 | 1
Red | A | 14/04/2017 | 17/02/2017 | 0
Red | A | 21/04/2017 | 24/02/2017 | 0
Red | A | 28/04/2017 | 3/03/2017 | 0
Red | A | 5/05/2017 | 10/03/2017 | 1
Red | B | 10/03/2017 | 13/01/2017 | 0
Red | B | 17/03/2017 | 20/01/2017 | 1
Red | B | 24/03/2017 | 27/01/2017 | 0
Red | B | 31/03/2017 | 3/02/2017 | 0
Red | B | 7/04/2017 | 10/02/2017 | 1
Red | B | 14/04/2017 | 17/02/2017 | 1
Red | B | 21/04/2017 | 24/02/2017 | 1
Red | B | 28/04/2017 | 3/03/2017 | 0
Red | B | 5/05/2017 | 10/03/2017 | 1
Blue | E | 10/03/2017 | 13/01/2017 | 0
Blue | E | 17/03/2017 | 20/01/2017 | 1
Blue | E | 24/03/2017 | 27/01/2017 | 1
Blue | E | 31/03/2017 | 3/02/2017 | 0
Blue | E | 7/04/2017 | 10/02/2017 | 1
Blue | E | 14/04/2017 | 17/02/2017 | 0
Blue | E | 21/04/2017 | 24/02/2017 | 0
Blue | E | 28/04/2017 | 3/03/2017 | 0
Blue | E | 5/05/2017 | 10/03/2017 | 1
I have created a calculated field called 8WeeksSum
8WeeksSum = CALCULATE(SUM([count]), FILTER(ALLSELECTED(Table1) ,Table1[Week ending date] <= MAX(Table1[Week ending date]) && Table1[Week ending date] > MAX(Table1[8 weeks ago date]) ) )
And when I tried to view data in a pivot table, below is the result that I see. Basically I cannot slice data on stream and product level, only date level. I reckon that ALLSELECTED function might play a role here but I'm really keen to understand the reason and how to solve this problem, any advice is welcomed, thanks a million!
Solved! Go to Solution.
HI @JacobLI,
My previous answer was based on the available information you gave didn't know you wanted to slice by additional levels of information, try this formula:
8WeeksSum = CALCULATE ( SUM ( [ Flag Count] ), FILTER ( ALLSELECTED ( Table2 ), Table2[ Week Ending Date ] <= MAX ( Table2[ Week Ending Date ] ) && Table2[ Week Ending Date ] > MAX ( Table2[ 8 weeks ago Date ] ) ), FILTER ( ALLSELECTED ( Table2[Stream ] ), Table2[Stream ] = MAX ( Table2[Stream ] ) ), FILTER ( ALLSELECTED ( Table2[ Product ] ), Table2[ Product ] = MAX ( Table2[ Product ] ) ) )
The end result is this:
Once again if you want that your final result compreend the entire table using slicers you should replace the ALLSELECTED by ALL
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @JacobLI,
My previous answer was based on the available information you gave didn't know you wanted to slice by additional levels of information, try this formula:
8WeeksSum = CALCULATE ( SUM ( [ Flag Count] ), FILTER ( ALLSELECTED ( Table2 ), Table2[ Week Ending Date ] <= MAX ( Table2[ Week Ending Date ] ) && Table2[ Week Ending Date ] > MAX ( Table2[ 8 weeks ago Date ] ) ), FILTER ( ALLSELECTED ( Table2[Stream ] ), Table2[Stream ] = MAX ( Table2[Stream ] ) ), FILTER ( ALLSELECTED ( Table2[ Product ] ), Table2[ Product ] = MAX ( Table2[ Product ] ) ) )
The end result is this:
Once again if you want that your final result compreend the entire table using slicers you should replace the ALLSELECTED by ALL
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Dear @MFelix, I was not in office until today, and I couldn't test out your solution. It works and thank you once again for helping me out!
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |