Help Needed: Needing Single Slicer for Multiple Columns in PBI Report Server Version (Cant use unpivot)

Power BI
1

[I am using Power BI desktop and Power BI report server, we do not currently have the service so I cannot use field parameters as they have not been introduced into the report server yet]

In a table from SQL, I have 6 columns that I am needing to be used under a single slicer as they are considered all a type of product. Each column contains the number of products sold for a specific date and time of that product. For example, on 12/1/2023 at 12:00 am, I can have numbers greater than 1 to represent that number of products for that date and time for each product type that was sold (so in my table in a single row I can have 0 for 1 of 6 product types to represent none were sold, and numbers greater than 1 for each of the 6 columns that that specific product was sold). Hopefully that makes sense to paint a picture of what I am working with here.

Up until recently I only had 3 product types [columns] and could unpivot the data to represent a single column and would have a result for each product sold and in the new unpivoted column it would label which type and I could count rows on that column to represent totals and then use a slicer on that column. The problem is that now we had to introduce 3 new product types and my result set went from 70 million rows to over 700 million rows with the unpivot method and I can barely get power bi to run. I am needing a solution for slicing the data on product type. I am struggling to find a workable method as of this time and am hoping someone else who has been in a similar predicament has figured something out.

2

Here is an example of the data (cant share the pbix as its data is sensitive):

DATE PRODUCT1 PRODUCT2 PRODUCT3 PRODUCT4 PRODUCT5 PRODUCT6
12/1/2023 12:00 50 15 0 0 0 45
12/1/2023 1:00 12 23 24 0 34 31
12/1/2023 2:00 1 0 28 0 0 20
12/1/2023 3:00 0 4 0 100 0 0
12/1/2023 4:00 0 8 0 12 34 0
12/1/2023 5:00 60 0 0 1 40 0
3

@teven I have three suggestions:

Replace the zeroes with “null” values. If you then unpivot those product/#sold combinations will not create a row in the unpivoted table. That means for your first row in your example data, would result in 3 rows only, instead of 7 rows

Do you need the time element of the products sold? If not, you can turn the data type of the date column in “date” and then group by “date”

You can also split the date column in 2 columns. One for the date and one for the time. A timestamp column (as it is now) is infamously slow. Splitting would like increase the overall speed (at least for Power Query refresh). And make make your dashboard easier to handle by using not only a slicer for product, but also for time.

Hope this helps.

4

Tyler,

You can do what you want without unpivoting the data in power query, but see the note at the bottom about performance with large datasets.

First, create a table that maps each product type to its respective column. Something that looks like:

image

ProductTypeMapping 
ProductTypeMapping = DATATABLE(
    "ProductType", STRING, 
    "ColumnName", STRING,
    {
        {"Product1", "PRODUCT1"},
        {"Product2", "PRODUCT2"},
        {"Product3", "PRODUCT3"},
        {"Product4", "PRODUCT4"},
        {"Product5", "PRODUCT5"},
        {"Product6", "PRODUCT6"}
    }
)

You can put ProductTypeMapping[ProductType] in a slicer.

Then, create a measure that dynamically aggregates the data based on the selected product type:

Total Products Sold = 
SUMX (
    VALUES ( 'ProductTypeMapping'[ProductType] )
    , CALCULATE (
        SUMX (
            'Sheet1'
            , SWITCH (
                VALUES ( 'ProductTypeMapping'[ColumnName] )
                , "PRODUCT1", 'Sheet1'[PRODUCT1]
                , "PRODUCT2", 'Sheet1'[PRODUCT2]
                , "PRODUCT3", 'Sheet1'[PRODUCT3]
                , "PRODUCT4", 'Sheet1'[PRODUCT4]
                , "PRODUCT5", 'Sheet1'[PRODUCT5]
                , "PRODUCT6", 'Sheet1'[PRODUCT6]
            )
        )
    )
)

There’s nested iterators here and they operate in different contexts. The first SUMX iterates over the ProductTypeMapping table. This table is used for the slicer, allowing users to select a product type. VALUES('ProductTypeMapping'[ProductType]) gets the distinct values of the product type selected in the slicer. CALCULATE is used to modify the context of the second SUMX function so that it calculates the sum based on the selected product type. The second SUMX iterates over your sales data table. SWITCH evaluates the current value of the ProductTypeMapping’s ColumnName, and, depending on this value, it returns the corresponding column from your main data table. Note, the outer SUMX is iterating over the distinct values of the product types, while the inner SUMX is iterating over the sales table.

example

This is one way to do it, but nested SUMX functions can potentially lead to performance issues, especially when dealing with large datasets like you have. It could be quite slow. There might be more efficient ways to do this…

1 Like
5

While this is a solution I was looking for, I immediately ran out of memory and was stopped by an error. I had an idea like this in my head, but couldnt quite get it right, so I appreciate you putting that together for me. Going to have to think about this for a little bit longer and see if there is anything else, but I am guessing with such a large dataset it is going to be extremely difficult to find a decent solution.