Re: Based on range

manideep547 · ‎01-29-2020

Hi,
I have 3 tables
1]date(calendar auto)
2]customer_Activity(State)
3]transaction (Id date )
->The date table contains dates.
->customer_Activity contains (one columns with two values "Active", "Inactive")
->transaction tables contains
Id date
1 2019-01-12
6 2019-07-11

2 2018-02-11
7 2018-09-05

3 2017-03-05
8 2017-09-06

4 2016-01-06
9 2016-05-06
9 2016-04-06

I have a date slicer (calendar auto) based on the set range I need the count of the active customers and inactive customers
when I set the range 1/1/2018 to 1/1/2019
the normal count should be "4" in the card but I need active customer count and inactive customers count based on datediff and in between date range in slicer below is the measure then

customer Relation = IF(DATEDIFF(SELECTEDVALUE(transaction [date]),MIN(Date[Date]),MONTH)>6,"Inactive","Active")
if I used above measure it showing the count of active and inactive irrespective of the slicer range
for the count of active and inactive, I used below formula

var _table=ADDCOLUMNS('transaction ',"_customer Relation",[customer Relation]) return

COUNTX(FILTER(_table,[_customer Relation]=SELECTEDVALUE(customer_Activity(State))),transaction [Id])
customer_Activity(State) slicer
customer Relation must work with range in slicer (date)
I need a distinct count of active and inactive
if the customer present in the first 6 months from the slicer is active if the customer is having + 6 months based on the slicer date is inactive customer
example :
if slicer range is 1/1/2018 to 1/1/2019 then result is a count of active is 1 and Inactive is 3---normal count is 4
if slicer range is 1/1/2017 to 1/1/2018 then result, count of active is 1 and Inactive is 3---normal count is 6
if slicer range is 1/1/2016 to 1/1/2019 then result, count of active, is 2 and Inactive is 6(distinct count )--normal count is 9

v-lionel-msft · ‎01-30-2020

Hi @manideep547 ,

Maybe you need to add a ALLSELECTED() function:

customer Relation = 
VAR x =
IF(
  DATEDIFF(
    SELECTEDVALUE(transaction [date]),
    MIN(Date[Date]),MONTH
  )>6,
  "Inactive","Active"
)
RETURN
CALCULATE(
  x,
  ALLSELECTED(Date[Date])
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎01-29-2020

for range date slicers you need to use min/max not Selectvalue().

Selectedvalue() will not work with range date slicer.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

manideep547 · ‎01-29-2020

If we use only min and max it is not pointing to that particular table(transaction tables).
Here we need to count the IDs based on the date selected in the slicer count of active and count of Inactive.
@Anonymous
Thanks and regards
CH MANI DEEP