Forum Discussion
DAX filter not working
Anonymous you can put data here as text,
also provide the measures used in the condition
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 | 195 | Nov-22 | |
| 1004 | 181 | Sep-22 | |
| 1004 | 297 | Sep-22 | |
| 1004 | 124 | Sep-22 | |
| 1004 | 283 | Oct-22 | |
| 1004 | 45 | Oct-22 | |
| 1005 | 239 | Sep-22 | |
| 1005 | 113 | Nov-22 | |
| 1005 | 231 | Oct-22 | |
| 1005 | 124 | Nov-22 | |
| 1005 | 156 | Dec-22 | |
| 1006 | 157 | Dec-22 | |
| 1006 | 61 | Dec-22 | |
| 1006 | 264 | Dec-22 | |
| 1006 | 175 | Sep-22 | |
| 1006 | 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 ago
Solution 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- Anonymous3 years agoNot applicable
Hello DimaMD ,
Thanks for your help! I applied the codes and it works.
Just one issue I am facing is one of my ranges are missing when I apply Amount raw/ Amount raw total.
Also, for better personal understanding, can I just check that I have understood the codes below correctly?
// Add new column (segment) in the consolidated table that allocates a revenue range to each transaction Amount raw = VAR _CustomerSegments = ADDCOLUMNS( VALUES('Raw data'[CustID]), "Segment", [Rev Range] ) // Count number of customers for each group in the new segment column that have data. Also can I ask what is # Customers? VAR _SegmentCustomerCount = GROUPBY( _CustomerSegments, [Segment], "# Customers", COUNTX ( CURRENTGROUP (), 1 ) ) // For each segment, filter for each range VAR _Result = FILTER( _SegmentCustomerCount, [Segment] = SELECTEDVALUE('Range table'[Range]) ) RETURN MAXX( _Result, [# Customers] )- DimaMD3 years ago
Solution Sage
Anonymous Hi, in my reports we counted the number of my customers and called this virtual column "# Customers"
Which of the ranges is not displayed for you? you have to give an example.
Also check the correct spelling of the condition, maybe there is an error somewhere, it may be banal spelling, you missed a space or some symbol