Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cel
Helper I
Helper I

DAX filter not working

‎01-10-2023 11:10 PM

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

 

 
Labels:
Message 1 of 12
876 Views
0
11 REPLIES 11
alekhved
Resolver I
Resolver I

‎01-10-2023 11:53 PM

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!

Message 9 of 12
819 Views
0
cel
Helper I
Helper I
In response to alekhved

‎01-11-2023 12:53 AM

Hello @alekhved , I tried the formula but DISTINCTCOUNT only accepts one argument. It shows an error if there is more than one input

Message 10 of 12
789 Views
0
alekhved
Resolver I
Resolver I
In response to cel

‎01-11-2023 01:11 AM

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!

Message 11 of 12
781 Views
0
cel
Helper I
Helper I
In response to alekhved

‎01-11-2023 06:31 PM

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.

Message 12 of 12
762 Views
0
cel
Helper I
Helper I

‎01-10-2023 11:52 PM

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?

Message 3 of 12
819 Views
0
DimaMD
Solution Sage
Solution Sage
In response to cel

‎01-10-2023 11:57 PM

@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
Message 4 of 12
813 Views
0
cel
Helper I
Helper I
In response to DimaMD

‎01-11-2023 12:19 AM
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!

Message 5 of 12
796 Views
0
DimaMD
Solution Sage
Solution Sage
In response to cel

‎01-12-2023 01:53 AM

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
cel.pbix
Message 6 of 12
747 Views
0
cel
Helper I
Helper I
In response to DimaMD

‎01-19-2023 06:08 PM

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

 

 

Message 7 of 12
687 Views
0
DimaMD
Solution Sage
Solution Sage
In response to cel

‎01-20-2023 12:54 AM

@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
Message 8 of 12
679 Views
0
DimaMD
Solution Sage
Solution Sage

‎01-10-2023 11:16 PM

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
Message 2 of 12
871 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All