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 ( SELECTEDVALUE ( 'RangeTable'[Range] ), "< $0", CALCULATE ( DISTINCTCOUNT ( 'Table'[CustID] ), FILTER ( 'Table', [Rev_Current YTD] < 0 ) ), "$0 to $100", CALCULATE ( DISTINCTCOUNT ( 'Table'[CustID] ), FILTER ( 'Table', [Rev_Current YTD] > 0 && [Rev_Current YTD] <= 100 ) ), "$101 to $500", CALCULATE ( DISTINCTCOUNT ( 'Table'[CustID] ), FILTER ( 'Table', [Rev_Current YTD] > 100 && [Rev_Current YTD] <= 500 ) ), "$501 to $1000", CALCULATE ( DISTINCTCOUNT ( 'Table'[CustID] ), FILTER ( 'Table', [Rev_Current YTD] > 500 && [Rev_Current YTD] <= 1000 ) ), "$1001 to $1500", CALCULATE ( DISTINCTCOUNT ( 'Table'[CustID] ), FILTER ( 'Table', [Rev_Current YTD] > 1000 && [Rev_Current YTD] <= 1500 ) ), "> $1500") This is what my visualisation in PBI shows. and this is my visualisation if it helps:

hi Anonymous Provide a dummy file for testing.

hi, Please try this Rev Range =SWITCH (SELECTEDVALUE ( 'RangeTable'[Range] ),"< $0",CALCULATE (DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] < 0 )),"$0 to $100",CALCULATE (DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 0 && [Rev_Current YTD] <= 100 )),"$101 to $500",CALCULATE (DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 100 && [Rev_Current YTD] <= 500 )),"$501 to $1000",CALCULATE (DISTINCTCOUNT ( 'Table'[CustID] , [Rev_Current YTD] > 500 && [Rev_Current YTD] <= 1000 )),"$1001 to $1500",CALCULATE (DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 1000 && [Rev_Current YTD] <= 1500 )),"> $1500") If this doesnt work, can you share the calculation used for Rev_current_YTD. that might be causing an issue. Thanks!

Hello DimaMD ,*To sum total revenueRev_Current YTD = sum('Table'[Revenue]) Raw data:CustIDCustNameRevenueDate1001Netflix400Oct-221001Netflix117Oct-221001Netflix98Dec-221001Netflix2345Nov-221001Netflix90Oct-221002Spotify277Sep-221002Spotify2455Nov-221002Spotify104Oct-221002Spotify654Nov-221002Spotify205Sep-221003Disney8Oct-221003Disney53Oct-221003Disney853Sep-221003Disney209Sep-221003Disney113Oct-221004Instagram195Nov-221004Instagram181Sep-221004Instagram297Sep-221004Instagram124Sep-221004Instagram283Oct-221004Instagram45Oct-221005Facebook239Sep-221005Facebook113Nov-221005Facebook231Oct-221005Facebook124Nov-221005Facebook156Dec-221006Google157Dec-221006Google61Dec-221006Google264Dec-221006Google175Sep-221006Google289Dec-221008Cloversoft192Dec-221008Cloversoft118Dec-221008Cloversoft214Sep-221008Cloversoft93Oct-221008Cloversoft277Sep-221009TWG21Nov-221009TWG27Nov-221009TWG295Nov-221009TWG152Nov-221009TWG3Dec-221009TWG271Dec-221009TWG18Sep-221011KFC29Dec-221011KFC222Oct-221011KFC227Sep-221011KFC71Dec-221012Long John Silver78Nov-221012Long John Silver41Dec-221012Long John Silver60Nov-221012Long John Silver70Dec-221013Burger King95Sep-221013Burger King300Dec-221013Burger King63Sep-221013Burger King8055Nov-221014Microsoft216Dec-221014Microsoft222Dec-221014Microsoft270Sep-221014Microsoft15Sep-221015Acer4Nov-221015Acer220Nov-221015Acer26Dec-221015Acer151Dec-221015Acer259Nov-221016Dell124Nov-221016Dell145Sep-221016Dell215Dec-221016Dell79Nov-221016Dell18Nov-221017Lenovo279Oct-221017Lenovo584Dec-221017Lenovo51Nov-221017Lenovo79Oct-221017Lenovo169Nov-221018Apple293Nov-221018Apple190Oct-221018Apple143Oct-221018Apple13Sep-221018Apple125Oct-221019Starbucks235Nov-221019Starbucks217Nov-221019Starbucks86Oct-221019Starbucks10Sep-221019Starbucks83Dec-221020Samsung73Dec-221020Samsung272Dec-221020Samsung115Dec-221020Samsung75Dec-221020Samsung107Oct-221007Lifebuoy0Dec-221007Lifebuoy0Sep-221007Lifebuoy0Oct-221007Lifebuoy0Nov-221007Lifebuoy0Dec-221010McDonalds0Oct-221010McDonalds0Sep-221010McDonalds0Oct-221010McDonalds0Nov-221010McDonalds0Dec-22 Range table00No Rev10.001100$ 0 to 1002101500$ 101 to 50035011000$ 501 to 1000410011500$ 1001 to 1500515011000000> $15006 Hope this helps!

DAX filter not working | Microsoft Fabric Community

11 Replies

DimaMD
Solution Sage
3 years ago
hi Anonymous Provide a dummy file for testing.

Anonymous

Not applicable

3 years ago

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?

DimaMD

Solution Sage

3 years ago

Anonymous you can put data here as text,
also provide the measures used in the condition

Anonymous

Not applicable

3 years ago

Hello DimaMD ,

*To sum total revenue

Rev_Current YTD = sum('Table'[Revenue])

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!

alekhved
Resolver I
3 years ago
hi,

Please try this

Rev Range =
SWITCH (
SELECTEDVALUE ( 'RangeTable'[Range] ),
"< $0",
CALCULATE (
DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] < 0 )
),
"$0 to $100",
CALCULATE (
DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 0 && [Rev_Current YTD] <= 100 )
),
"$101 to $500",
CALCULATE (
DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 100 && [Rev_Current YTD] <= 500 )
),
"$501 to $1000",
CALCULATE (
DISTINCTCOUNT ( 'Table'[CustID] , [Rev_Current YTD] > 500 && [Rev_Current YTD] <= 1000 )
),
"$1001 to $1500",
CALCULATE (
DISTINCTCOUNT ( 'Table'[CustID], [Rev_Current YTD] > 1000 && [Rev_Current YTD] <= 1500 )
),
"> $1500"
)

If this doesnt work, can you share the calculation used for Rev_current_YTD. that might be causing an issue.

Thanks!
- Anonymous
  Not applicable
  3 years ago
  Hello alekhved , I tried the formula but DISTINCTCOUNT only accepts one argument. It shows an error if there is more than one input
  - alekhved
    Resolver I
    3 years ago
    hi Anonymous
    
    My Bad!
    Please try this
    
    Rev Range =
    SWITCH (
    SELECTEDVALUE ( 'RangeTable'[Range] ),
    "< $0",
    CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustID]), [Rev_Current YTD] < 0 ),
    "$0 to $100",
    CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustID]), [Rev_Current YTD] > 0 && [Rev_Current YTD] <= 100 ),
    "$101 to $500",
    CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustID]), [Rev_Current YTD] > 100 && [Rev_Current YTD] <= 500 ),
    "$501 to $1000",
    CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustID]) , [Rev_Current YTD] > 500 && [Rev_Current YTD] <= 1000 ),
    "$1001 to $1500",
    CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustID]), [Rev_Current YTD] > 1000 && [Rev_Current YTD] <= 1500 ),
    "> $1500"
    )
    
    Thanks!

Forum Discussion

DAX filter not working

11 Replies

Related content

Filters not working properly

Running Total DAX not working with multiple filters

DAX - Filtering not working

Dax not working

DAX FILTER with multiple criteria