Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a customer table that contains:
I would like to create a slicer that can list all customers that were active between two dates. I want to conduct this efficiently Without enumerating. This example only uses enumeration:
Solved! Go to Solution.
Hey,
basically this is not as simple as it should be / could be, this is due to the following
Nevertheless my solution needs an additional table with date values, this table is not related to any date column of the customer table. This table is used to select a date range, e.g. by using the date slicer.
Then I created a measure that returns the value 1 if the customer can be considered during the selected time period:
Check Active Customer = var minDate = MINX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]) var maxDate = MAXX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]) return SUMX( 'Customer' , var custStartdate = 'Customer'[Startdate] var custEnddate = 'Customer'[Enddate] return IF( AND(custEnddate >= minDate, custStartdate <= maxDate) ,1 ,BLANK() ) )
Here is a screenshot of a little report I created:
I'm using
Personally my favorite solution is the "simple" bar chart, because here i can use the measure inside the visual level filter, this means I can use addtional measures to "provide" additional information about the customer.
The Disadvantage using a bar chart that the user of the report has to be accustomed to use the CTRL Key, if cross filtering has
to be used.
On the other hand "formatting" of the Attribute Slicer becomes more complex.
Hopefully this gets you started,
Regards,
Tom
Hey,
basically this is not as simple as it should be / could be, this is due to the following
Nevertheless my solution needs an additional table with date values, this table is not related to any date column of the customer table. This table is used to select a date range, e.g. by using the date slicer.
Then I created a measure that returns the value 1 if the customer can be considered during the selected time period:
Check Active Customer = var minDate = MINX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]) var maxDate = MAXX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]) return SUMX( 'Customer' , var custStartdate = 'Customer'[Startdate] var custEnddate = 'Customer'[Enddate] return IF( AND(custEnddate >= minDate, custStartdate <= maxDate) ,1 ,BLANK() ) )
Here is a screenshot of a little report I created:
I'm using
Personally my favorite solution is the "simple" bar chart, because here i can use the measure inside the visual level filter, this means I can use addtional measures to "provide" additional information about the customer.
The Disadvantage using a bar chart that the user of the report has to be accustomed to use the CTRL Key, if cross filtering has
to be used.
On the other hand "formatting" of the Attribute Slicer becomes more complex.
Hopefully this gets you started,
Regards,
Tom
this is wonderful,
I should have reclarified my question, user places one date, and check if that date is between CustomerBegindate and CustomerEndate
example, report user selects: 3/5/2018
it will pickup anything between begindate <= 3/5/2018 <= enddate,
I assume this will work also?
Check Active Customer = var SelectDate = MINX(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]) return SUMX( 'Customer' , var custStartdate = 'Customer'[Startdate] var custEnddate = 'Customer'[Enddate] return IF( AND(custEnddate >= SelectDate, custStartdate <= SelectDate) ,1 ,BLANK() ) )
Sure,
please do not forget to mark my / your post as answer, it will help others.
Regards,
Tom
will do, one more question, I am using a date dimension table,
I cannot search for a date,
1) either I have to scroll down a long list or use
2) a between filter, and input the single date twice?
Is there any way I can just enter one date in the slicer?
Hey,
I'm not sure if the default slicer provides an "input" field, but maybe one of the different options available if you switch through
provides something more appropriate for you requirement.
Maybe you can also check the Calendar / Date visuals available in the marketplace.
Regards,
Tom
by the way, if we have 50 million rows, do you think this is the most scalable way to conduct calculations in a new column? or do you have a more efficient/faster method? I can post another question, let me know and send points. I don't think enumeration method is scalable either. Is there a third method for larger data sets?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |