Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ( ...
Need Help
DimaMD's avatar
DimaMD
Solution Sage
3 years ago

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


I am attaching the file

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

 

  • DimaMD's avatar
    DimaMD
    Solution Sage
    3 years ago

    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