Create Calculated Column for each ID (to be used in x-axis of a bar and with a date slicer)

[PBI_jiao]

Hello folks,

We have a table 'Submission' containing on which days a person submitted a record. We would like to show percentage range of the submission on a bar chart, based on slicers (of date range selected and county selected). 'County' won't change for a person.

1. Table: Submission

2. A date table is created in Power BI desktop:

DateTable = CALENDAR ( Date(2020, 08, 12), Date(2021,08,31))

A measure is created in 'DateTable' to calculate how many days are there between the selected date range. It will be used for a slicer.

Days Count on Calendar =

VAR selected =

ALLSELECTED (DateTable)

VAR

_MIN = MINX(selected, DateTable[Date] )

VAR

_MAX = MAXX(selected, DateTable[Date] )

RETURN

DATEDIFF ( _MIN, _MAX, DAY)+1

3. One to many relationship on DateTable[Date] and Submission[Submission_date] has been defined in PBI desktop:

4. The calculation I did in table 'Submission':

1) a Measure to count how many submissions for each person during selected date range (in the slicer in step 2):

Count_Submission =

VAR FirstDay = CALCULATE(

MIN(DateTable[Date]),

ALLSELECTED(DateTable[Date])

)

VAR LastDay = CALCULATE(

MAX(DateTable[Date]),

ALLSELECTED(DateTable[Date])

)

RETURN

CALCULATE(

COUNTA([submission_date]),

DATESBETWEEN(DateTable[Date],FirstDay,LastDay)

)+0

2) A measure to calculate the submission percentage  (for example, if 003 submitted 6 times during 3/1/21 through 3/15/21, the percentage is 6 times/15 days = 40%.

Measure_Percent = Divide( [Count_Submission], DateTable[Days Count on Calendar])*100

3) A measure to assign the percentage range:

Neasure_Range =

SWITCH(

TRUE(),

[Measure_Percent]>=0 && [Measure_Percent]<=20,"0-20%",

[Measure_Percent]>20 && [Measure_Percent]<=40,"21-40%",

[Measure_Percent]>40 && [Measure_Percent]<=60,"41-60%",

[Measure_Percent]>60 && [Measure_Percent]<=80,"61-80%",

[Measure_Percent]>80 && [Measure_Percent]<=100,"81-100%"

)

It all look right so far, and data in the table visual changes according to the selection in both slicer.

5. Next I want to use a bar chart to present the percentage range, but 'Measure_range' can't be used in x-axis of a bar. 

So I create a calculated column 'Column_Range', but the range is not right because it is calculate for each row instead of each id.  The bar has 'Column_Range' in x-axis and 'count of Column_Range' in value. It shows '0-20%' only. It still changes with the selections in both slicers.

So, the question is, how can I make the column calculate for each ID instead of each row? If that is not possible, is there any other ways to achieve what I want to do?

Here is the code I have currently:

Column_Range =

SWITCH(

TRUE(),

[Measure_Percent]>=0 && [Measure_Percent]<=20,"0-20%",

[Measure_Percent]>20 && [Measure_Percent]<=40,"21-40%",

[Measure_Percent]>40 && [Measure_Percent]<=60,"41-60%",

[Measure_Percent]>60 && [Measure_Percent]<=80,"61-80%",

[Measure_Percent]>80 && [Measure_Percent]<=100,"81-100%"

)

Thanks a lot for your time! 

[lbendlin]

Don't have an answer (maybe you can provide sample data in usable form?)  but be aware that your SWITCH statement can be simplified, and also should be extended to cover situations over 100%

Column_Range =

SWITCH(

TRUE(),

[Measure_Percent]<=20,"0-20%",

[Measure_Percent]<=40,"21-40%",

[Measure_Percent]<=60,"41-60%",

[Measure_Percent]<=80,"61-80%",

[Measure_Percent]<=100,"81-100%",

"Over 100%"

)

[PBI_jiao]

Thanks! Here is the data: