Forum Discussion
Based on range
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
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
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
4 Replies
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/913784If 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 DEEPwhy you mean by this "If we use only min and max it is not pointing to that particular table(transaction tables)"
you can use any tablle column with min and max.
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/913784
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
