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 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.