Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TapZxK
Helper II
Helper II

Help needed with switch column and sum

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. 

TapZxK_2-1675267040460.png

TapZxK_3-1675267099810.png

 

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

 

Thank You.

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

vjingzhang_0-1675414326265.png

 

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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"
)

 

vjingzhang_0-1675303679077.png

 

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.

TapZxK_0-1675368971868.png

But if in the filter pane I enable to only display Booked To A Project I'm getting different results.

TapZxK_1-1675369056390.png

 

Any thoughts why this could be happenning?

I have uploaded PBIX file here

Best Regards,

Kris

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.  

vjingzhang_0-1675414326265.png

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.