Forum Discussion
Help needed with switch column and sum
Hi Anonymous
This is because some names have multiple calendar types, which leads to SELECTEDVALAUE returns the blank value. SELECTEDVALUE function returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult. In my sample file, I added "Calendar Type" column to the table visual, so for every row it only has one calendar type value. In your file, you don't have "Calendar Type" in the table, so it will return the alternate blank value for those rows which have more than one calendar types. Once you add a filter to select only "Booked To A Project", SELECTEDVALUE will return that value correctly.
If you only want to display "Name", "Total Project Allocation" and "Evaluation" in the table visual, and as [Total Project Allocation] already has a filter for only "Booked To A Project", you can try below measure. It will return the same result as that in your second screenshot.
Evaluation = SWITCH( TRUE(), [Total Project Allocation] < 1, "Partially Available", [Total Project Allocation] > 1, "Overbooked", "Fully Booked" )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi Anonymous
Your guess is correct. A calculated column is evaluated for every row in the table, so it will get an "Availability" value for every row. If you want to aggregate the sum of allocated for every person in a table visual, you can create a measure similar to below and add it to the visual.
Availability =
SWITCH (
TRUE (),
SELECTEDVALUE('Individual Status Raw DATA'[Calendar Type]) = "Booked To A Project"
&& SUM('Individual Status Raw DATA'[Allocated]) < 1, "Partially Available",
SELECTEDVALUE('Individual Status Raw DATA'[Calendar Type]) = "Booked To A Project"
&& SUM('Individual Status Raw DATA'[Allocated]) > 1, "Overbooked",
"Not Available"
)
In addition, you have a [First Available Day After Last Client Assignment] column in your calculated column formula, does this column have the same date value for a person? If it has the same value and you want to use it to affect the measure, you can use SELECTEDVALUE to surround it in the measure formula. If it has different values and you want to use the first date of them to affect the measure, you can use MIN instead of SELECTEDVALUE. I don't see this column in the sample data so I don't include it in my sample measure.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi v-jingzhang,
I tested your solution and it works alas not fully. I'm not really sure why this is.
here is what's going on.
when I add your measure to the table Visual I'm getting inconsistent results, as you can see below, some people who have over 1 Allocation are appearing as Fully Booked.
But if in the filter pane I enable to only display Booked To A Project I'm getting different results.
Any thoughts why this could be happenning?
I have uploaded PBIX file here
Best Regards,Kris
Hi Anonymous
This is because some names have multiple calendar types, which leads to SELECTEDVALAUE returns the blank value. SELECTEDVALUE function returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult. In my sample file, I added "Calendar Type" column to the table visual, so for every row it only has one calendar type value. In your file, you don't have "Calendar Type" in the table, so it will return the alternate blank value for those rows which have more than one calendar types. Once you add a filter to select only "Booked To A Project", SELECTEDVALUE will return that value correctly.
If you only want to display "Name", "Total Project Allocation" and "Evaluation" in the table visual, and as [Total Project Allocation] already has a filter for only "Booked To A Project", you can try below measure. It will return the same result as that in your second screenshot.
Evaluation = SWITCH( TRUE(), [Total Project Allocation] < 1, "Partially Available", [Total Project Allocation] > 1, "Overbooked", "Fully Booked" )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.