Forum Discussion
Anonymous
3 years agoNot applicable
DAX filter not working
I am trying to display the number of customers that fall within each revenue range. This is the code I have right now. However, the filter does not seem to be working. Rev Range = SWITCH ( ...
Anonymous
3 years agoNot applicable
Hello DimaMD , there is no button for me to upload the files and filesharing webs are blocked by my company laptop. Would it help if I screenshot the data?
- DimaMD3 years agoSolution Sage
Anonymous you can put data here as text,
also provide the measures used in the condition- Anonymous3 years agoNot applicable
Raw data:
CustID CustName Revenue Date 1001 Netflix 400 Oct-22 1001 Netflix 117 Oct-22 1001 Netflix 98 Dec-22 1001 Netflix 2345 Nov-22 1001 Netflix 90 Oct-22 1002 Spotify 277 Sep-22 1002 Spotify 2455 Nov-22 1002 Spotify 104 Oct-22 1002 Spotify 654 Nov-22 1002 Spotify 205 Sep-22 1003 Disney 8 Oct-22 1003 Disney 53 Oct-22 1003 Disney 853 Sep-22 1003 Disney 209 Sep-22 1003 Disney 113 Oct-22 1004 Instagram 195 Nov-22 1004 Instagram 181 Sep-22 1004 Instagram 297 Sep-22 1004 Instagram 124 Sep-22 1004 Instagram 283 Oct-22 1004 Instagram 45 Oct-22 1005 Facebook 239 Sep-22 1005 Facebook 113 Nov-22 1005 Facebook 231 Oct-22 1005 Facebook 124 Nov-22 1005 Facebook 156 Dec-22 1006 Google 157 Dec-22 1006 Google 61 Dec-22 1006 Google 264 Dec-22 1006 Google 175 Sep-22 1006 Google 289 Dec-22 1008 Cloversoft 192 Dec-22 1008 Cloversoft 118 Dec-22 1008 Cloversoft 214 Sep-22 1008 Cloversoft 93 Oct-22 1008 Cloversoft 277 Sep-22 1009 TWG 21 Nov-22 1009 TWG 27 Nov-22 1009 TWG 295 Nov-22 1009 TWG 152 Nov-22 1009 TWG 3 Dec-22 1009 TWG 271 Dec-22 1009 TWG 18 Sep-22 1011 KFC 29 Dec-22 1011 KFC 222 Oct-22 1011 KFC 227 Sep-22 1011 KFC 71 Dec-22 1012 Long John Silver 78 Nov-22 1012 Long John Silver 41 Dec-22 1012 Long John Silver 60 Nov-22 1012 Long John Silver 70 Dec-22 1013 Burger King 95 Sep-22 1013 Burger King 300 Dec-22 1013 Burger King 63 Sep-22 1013 Burger King 8055 Nov-22 1014 Microsoft 216 Dec-22 1014 Microsoft 222 Dec-22 1014 Microsoft 270 Sep-22 1014 Microsoft 15 Sep-22 1015 Acer 4 Nov-22 1015 Acer 220 Nov-22 1015 Acer 26 Dec-22 1015 Acer 151 Dec-22 1015 Acer 259 Nov-22 1016 Dell 124 Nov-22 1016 Dell 145 Sep-22 1016 Dell 215 Dec-22 1016 Dell 79 Nov-22 1016 Dell 18 Nov-22 1017 Lenovo 279 Oct-22 1017 Lenovo 584 Dec-22 1017 Lenovo 51 Nov-22 1017 Lenovo 79 Oct-22 1017 Lenovo 169 Nov-22 1018 Apple 293 Nov-22 1018 Apple 190 Oct-22 1018 Apple 143 Oct-22 1018 Apple 13 Sep-22 1018 Apple 125 Oct-22 1019 Starbucks 235 Nov-22 1019 Starbucks 217 Nov-22 1019 Starbucks 86 Oct-22 1019 Starbucks 10 Sep-22 1019 Starbucks 83 Dec-22 1020 Samsung 73 Dec-22 1020 Samsung 272 Dec-22 1020 Samsung 115 Dec-22 1020 Samsung 75 Dec-22 1020 Samsung 107 Oct-22 1007 Lifebuoy 0 Dec-22 1007 Lifebuoy 0 Sep-22 1007 Lifebuoy 0 Oct-22 1007 Lifebuoy 0 Nov-22 1007 Lifebuoy 0 Dec-22 1010 McDonalds 0 Oct-22 1010 McDonalds 0 Sep-22 1010 McDonalds 0 Oct-22 1010 McDonalds 0 Nov-22 1010 McDonalds 0 Dec-22 Range table
0 0 No Rev 1 0.001 100 $ 0 to 100 2 101 500 $ 101 to 500 3 501 1000 $ 501 to 1000 4 1001 1500 $ 1001 to 1500 5 1501 1000000 > $1500 6 Hope this helps!
- DimaMD3 years agoSolution Sage
Hi Anonymous Sorry for the delay in replying. Here is your solution.
The first thing you need to do is write a measure that will determine the RangeRev Range = SWITCH( TRUE(), AND([Rev_Current YTD] >= 0, [Rev_Current YTD] <= 100), "$ 0 to 100", AND([Rev_Current YTD] >= 101, [Rev_Current YTD] <= 500), "$ 101 to 500", AND([Rev_Current YTD] >= 501, [Rev_Current YTD] <= 1000), "$ 501 to 1000", AND([Rev_Current YTD] >= 1001, [Rev_Current YTD] <= 1499), "$ 1001 to 1500", [Rev_Current YTD] > 1500, "> $1500" )The next step is to see to which CustID the Range is assigned and calculate the quantity as a measure
Amount raw = VAR _CustomerSegments = ADDCOLUMNS( VALUES('Raw data'[CustID]), "Segment", [Rev Range] ) VAR _SegmentCustomerCount = GROUPBY( _CustomerSegments, [Segment], "# Customers", COUNTX ( CURRENTGROUP (), 1 ) ) VAR _Result = FILTER( _SegmentCustomerCount, [Segment] = SELECTEDVALUE('Range table'[No Rev]) ) RETURN MAXX( _Result, [# Customers] )We count the total number by No Rav
Amount raw total = IF( HASONEVALUE('Range table'[No Rev]), [Amount raw], SUMX(VALUES('Range table'[No Rev]), [Amount raw]) )
I am attaching the file