Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cel
Helper I
Helper I

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 (
    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.
cel_2-1673420292358.png    cel_3-1673420311444.png

 

and this is my visualisation if it helps:

cel_4-1673420986989.png

 

 
11 REPLIES 11
alekhved
Resolver I
Resolver I

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 @alekhved , I tried the formula but DISTINCTCOUNT only accepts one argument. It shows an error if there is more than one input

hi @cel 

 

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!

Hello @alekhved ,

 

This is the error message that I received:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

cel
Helper I
Helper I

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?

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Hello @DimaMD ,
*To sum total revenue
Rev_Current YTD = sum('Table'[Revenue])

 

Raw data:

CustIDCustNameRevenueDate
1001Netflix400Oct-22
1001Netflix117Oct-22
1001Netflix98Dec-22
1001Netflix2345Nov-22
1001Netflix90Oct-22
1002Spotify277Sep-22
1002Spotify2455Nov-22
1002Spotify104Oct-22
1002Spotify654Nov-22
1002Spotify205Sep-22
1003Disney8Oct-22
1003Disney53Oct-22
1003Disney853Sep-22
1003Disney209Sep-22
1003Disney113Oct-22
1004Instagram195Nov-22
1004Instagram181Sep-22
1004Instagram297Sep-22
1004Instagram124Sep-22
1004Instagram283Oct-22
1004Instagram45Oct-22
1005Facebook239Sep-22
1005Facebook113Nov-22
1005Facebook231Oct-22
1005Facebook124Nov-22
1005Facebook156Dec-22
1006Google157Dec-22
1006Google61Dec-22
1006Google264Dec-22
1006Google175Sep-22
1006Google289Dec-22
1008Cloversoft192Dec-22
1008Cloversoft118Dec-22
1008Cloversoft214Sep-22
1008Cloversoft93Oct-22
1008Cloversoft277Sep-22
1009TWG21Nov-22
1009TWG27Nov-22
1009TWG295Nov-22
1009TWG152Nov-22
1009TWG3Dec-22
1009TWG271Dec-22
1009TWG18Sep-22
1011KFC29Dec-22
1011KFC222Oct-22
1011KFC227Sep-22
1011KFC71Dec-22
1012Long John Silver78Nov-22
1012Long John Silver41Dec-22
1012Long John Silver60Nov-22
1012Long John Silver70Dec-22
1013Burger King95Sep-22
1013Burger King300Dec-22
1013Burger King63Sep-22
1013Burger King8055Nov-22
1014Microsoft216Dec-22
1014Microsoft222Dec-22
1014Microsoft270Sep-22
1014Microsoft15Sep-22
1015Acer4Nov-22
1015Acer220Nov-22
1015Acer26Dec-22
1015Acer151Dec-22
1015Acer259Nov-22
1016Dell124Nov-22
1016Dell145Sep-22
1016Dell215Dec-22
1016Dell79Nov-22
1016Dell18Nov-22
1017Lenovo279Oct-22
1017Lenovo584Dec-22
1017Lenovo51Nov-22
1017Lenovo79Oct-22
1017Lenovo169Nov-22
1018Apple293Nov-22
1018Apple190Oct-22
1018Apple143Oct-22
1018Apple13Sep-22
1018Apple125Oct-22
1019Starbucks235Nov-22
1019Starbucks217Nov-22
1019Starbucks86Oct-22
1019Starbucks10Sep-22
1019Starbucks83Dec-22
1020Samsung73Dec-22
1020Samsung272Dec-22
1020Samsung115Dec-22
1020Samsung75Dec-22
1020Samsung107Oct-22
1007Lifebuoy0Dec-22
1007Lifebuoy0Sep-22
1007Lifebuoy0Oct-22
1007Lifebuoy0Nov-22
1007Lifebuoy0Dec-22
1010McDonalds0Oct-22
1010McDonalds0Sep-22
1010McDonalds0Oct-22
1010McDonalds0Nov-22
1010McDonalds0Dec-22

 

Range table

00No Rev1
0.001100$ 0 to 1002
101500$ 101 to 5003
5011000$ 501 to 10004
10011500$ 1001 to 15005
15011000000> $15006

 

Hope this helps!

Hi @cel  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 Range

Rev 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]) )

Screenshot_29.jpg
I am attaching the file


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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]
   )

 

 

@cel 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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
DimaMD
Solution Sage
Solution Sage

hi @cel  Provide a dummy file for testing.


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.