Forum Discussion
Slicer help!
Hello, I have created on Power BI a matrix visual showcasing by campaigns, the number of orders that happened between 0 to 30 days from seeing an ad to an actual purchase (please see screenshot below). I have created a column that have grouped the days from the date_difference column and brought this into the matrix visual, so for example the 2 days would be the number of customers who have purchased the same day they saw the ad and up to 2 days later.
The stakeholders now want a slicer containing the date_difference column (so this is the column that has values ranging between 0 to 30). However he wants it such that when the value of 5 is selected on the slicer, the columns in the matrix will show all the values in the 2 Days column and in the 5 Days column:
and if the value of 7 is selected in the date_difference slicer, then all the values of the 2 Days and 5 Days column will be shown as well as some values from the 10 Days column (because 7 days would fall under the 10 days group):
How do I create such a column that I can bring in as a slicer to do the above. Thanks in advance!
Thanks for the reply from foodd please allow me to provide another insight:
Hi, Anonymous
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Generate a custom column to change its data type to an integer:
textText.Start([DAYS],2)3.To create a calculated table as a slicer:
slicer = GENERATESERIES(0,30,1)4. Below are the measure I've created for your needs:
MEASURE = VAR select1 = SELECTEDVALUE ( 'slicer'[Value] ) RETURN SWITCH ( TRUE (), select1 <= 2, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] <= 2 ), select1 <= 5, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 10 ), select1 <= 10, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 15 ), select1 <= 15, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 20 ), select1 <= 20, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 25 ), select1 <= 25, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 30 ), SUM ( 'purchase'[values] ) )5.Modify the visualization setting:
6.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello Anonymous , and thank you for sharing a question with the Community. This reply is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!
Thanks for the reply from foodd please allow me to provide another insight:
Hi, Anonymous
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Generate a custom column to change its data type to an integer:
textText.Start([DAYS],2)3.To create a calculated table as a slicer:
slicer = GENERATESERIES(0,30,1)4. Below are the measure I've created for your needs:
MEASURE = VAR select1 = SELECTEDVALUE ( 'slicer'[Value] ) RETURN SWITCH ( TRUE (), select1 <= 2, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] <= 2 ), select1 <= 5, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 10 ), select1 <= 10, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 15 ), select1 <= 15, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 20 ), select1 <= 20, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 25 ), select1 <= 25, CALCULATE ( SUM ( 'purchase'[values] ), 'purchase'[Custom] < 30 ), SUM ( 'purchase'[values] ) )5.Modify the visualization setting:
6.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
