Treshhold Trigger with Selectedvalue and OR Statement

DAX DAX Calculations
1

Hi All,

I have further developed your concept “treshold trigger” for a product rating (fast seller). For a differentiated evaluation of the products I have different threshold values for different product groups. In my example I have a threshold value at KPI LUG 3.7 for the product groups Tops all articles above this value should be displayed. Since several categories apply to the product group Tops, I combined this via Selectedvalue and OR Statement. However, I still see articles below the threshold value. What is your idea and why?

image
image.png543×657 42.3 KB

My Measure is:

ROST-Test Fast Seller Threshold Trigger = 
Var HAKAOuterwearROSTThreshold = CALCULATE(SELECTEDVALUE('HAKA Outerwear Metric'[Amounts]); 'HAKA Outerwear Metric'[Target Metric Outerwear] = "ROST")
Var HAKATrousersROSTThreshold = CALCULATE(SELECTEDVALUE('HAKA Trousers Metric'[Amounts]); 'HAKA Trousers Metric'[Target Metric Trousers] = "ROST")
Var HAKATopsROSTThreshold = CALCULATE(SELECTEDVALUE('HAKA TOPS Metric'[Amounts]); 'HAKA TOPS Metric'[Target Metric Tops] = "ROST")


Var ROSTThreshold = CALCULATE(SELECTEDVALUE('Fastseller threshold'[Amounts]); 'Fastseller threshold'[Target Metric] = "ROST")
Var MarginThreshold = CALCULATE(SELECTEDVALUE('Fastseller threshold'[Amounts]); 'Fastseller threshold'[Target Metric] = "Profit Margin")
var StockThreshold =  CALCULATE(SELECTEDVALUE('Fastseller threshold'[Amounts]); 'Fastseller threshold'[Target Metric] = "Stock qty") 

Return
SWITCH(TRUE();
//Target Metric HAKA Outerwear
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1100 || 1101 || 1102 || 1103 || 1104;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKAOuterwearROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Trousers                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1105;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKATrousersROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Tops                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1112|| 1110 || 1111;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] > HAKATopsROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 )))
2

Quite a bit going on here but will give it a go.

The way to think about it is to narrow down on one specific result you don’t think it right and then work through your formula slowly and see where the logic isn’t working properly.

Potentially there is a small operator not around the correct way, or that particularly result isn’t being captured at all within your formula logic.

Another strategy would be to filter the table down more so you are just reviewing a couple of items. This makes it easier on the mind when reviewing complex formulas.

If you can find out the issue with only just one result here you will figure out what’s going on quite fast. So really drill into one result and work through your formula that way.

Chrs
Sam

3

Hey Sam, I’ve tested a bit further and the measure and logic of the trigger work but there’s a problem with the multiple or statements per main product classification Outerwear | Trousers | Tops. Here’s my test: if I only use OR in this example //Target Metric HAKA Tops in a switch statement with selectedvalue the logic works and only values above the threshold are displayed.

SWITCH(TRUE();
//Target Metric HAKA Outerwear
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1103;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKAOuterwearROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Trousers                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1105;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKATrousersROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Tops                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1112 || 1111 || 1100;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKATopsROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ))

image
image.png448×647 42.5 KB

If I use OR in several Switch Statements with selectedvalue this example //Target Metric HAKA Outerwear and //Target Metric HAKA Tops the logic does not work and values below the threshold value are displayed again.

SWITCH(TRUE();
//Target Metric HAKA Outerwear
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1100 || 1101 || 1102 || 1103;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKAOuterwearROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Trousers                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1105;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKATrousersROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ));
//Target Metric HAKA Tops                        
        SELECTEDVALUE('Main product group'[Main Product Group ID])=1112 || 1111 || 1100;
        CALCULATE([ROST Product];
            FILTER(VALUES('Sales Seasonal Product'[Model Dim]);
                    COUNTROWS(FILTER('Fastseller threshold';
                        [ROST Product] >= HAKATopsROSTThreshold &&
                        [Product Profit Margin %] >= MarginThreshold &&
                        [Stock Qty seasonal product last date] > StockThreshold)) > 0 ))

image
image.png406×643 49 KB

I hope this brings us closer to making the error. I can’t explain the deviations yet. Greetings Mario

4

Still finding it difficult to locate the exact error here.

Have you though about breaking out the specific part of the formula that isn’t working for a result and then working through that way.

For me there’s just way to much logic within the formula the really figure out the one place where it’s breaking down.

I just recommended breaking out the formula piece by piece and you’ll find the problem quite quickly I would say.

5

Hi Sam, I have found the solution. As often by so complexe themes it was quiet easy. My OR Statement was not correct an i have change the dimension context in the selectedvalue statement. Have a look at my changes.

The old one:
SELECTEDVALUE('Main product group'[Main Product Group ID])=1100 || 1101 || 1102 || 1103

The new one: I have change from the dim table to the fact table
SELECTEDVALUE('Sales seasonal product'[Main Product Group ID])=1100 || SELECTEDVALUE('Sales seasonal product'[Main Product Group ID])=1101 || SELECTEDVALUE('Sales seasonal product'[Main Product Group ID])=1102 || SELECTEDVALUE('Sales seasonal product'[Main Product Group ID])= 1103
It works fine, but the operations are quiet hard, so the performance is very slow and power bi gives me sometimes an error. “My Memory is not enough”

Do you have an idea, how i can boost the performance or reduce the operations in the formula?

6

I would look to group these within your table with another column so that you don’t need all these OR statements. This would be easy to complete.