Help needed with switch column and sum

[TapZxK]

Hi Guys, 

I have created the following calculated column in my Data table. 

Availability =
SWITCH (
    TRUE (),
    'Individual Status Raw DATA'[Calendar Type] = "Booked To A Project"
        && 'Individual Status Raw DATA'[Allocated] < 1, "Partially Available",
    'Individual Status Raw DATA'[Calendar Type] = "Booked To A Project"
        && 'Individual Status Raw DATA'[Allocated] > 1, "Overbooked",
    'Individual Status Raw DATA'[First Available Day After Last Client Assignment]
        <= TODAY (), "Available Now",
    'Individual Status Raw DATA'[First Available Day After Last Client Assignment]
        - TODAY () <= 14, "available soon - within 2 weeks",
    'Individual Status Raw DATA'[First Available Day After Last Client Assignment]
        - TODAY () <= 30, "available soon - within 4 weeks",
    "Not Available"
)

Unfortunately, I'm not getting 100% of what I'm looking for. 

In the Individual Status Raw Data table I have cases where a person is "Booked to a Project" multiple times like so:

     Name // Calendar Type // Date // Allocated

Schatz // Booked To A Project // 30-Sep-2023 // 0.5

Schatz // Booked To A Project // 30-Oct-2023 // 0.5

Klat // Booked To A Project // 28-Nov-2023 // 0.5

Klat  // Booked To A Project // 30-Dec-2023 // 0.6

John // Booked To A Project // 15-Nov-2023 // 1

Sarah // Booked To A Project // 31-Dec-2023 // 0.8

When I'm brining in information on to a Table visual in Power BI, I would expect for "Schatz" to apper as "Not Available" as the sum of his Allocation is 1 but for Klat to appear as "Overbooked" as his total alocation is 1.1

Instead what I'm seeing in table visual is both Schatz and Klat appearing as "Partially Available" I guess this is because Power BI iterates line by line and does not see "Allocated" as a sum. 

How could I amend my formula or what do I need to add for it to work as I have planned?

Thank You.

[v-jingzhang]

Hi @TapZxK 

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.

[TapZxK]

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

[v-jingzhang · Accepted solution]

Hi @TapZxK 

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.