"More than" "Less than" text logic in filter

[setis]

Dear experts,

I'm trying to create a measure or calculated column that achieves the "Fee (desired result) column in the below table here:

I have so far: 

Column = 

CALCULATE(MAX('Fee Rules'[Fee]);
FILTER('Fee Rules';
'Fee Rules'[Customer] = Case_table[Customer]
&& 'Fee Rules'[Case Type] = Case_table[Case_type]
&& 'Fee Rules'[Cover] = Case_table[Cover]))

However, I don't now how to implement a logic for the "More than" and "Less than". 

Could somebody help, please?

[v-lid-msft · Accepted solution]

Hi @setis ,

We can try to create a caluclated column using following formula to meet your requirement:

Fee (desired result) = 
VAR t_customer =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Case_Type] = 'Case table'[Case_Type]
                && 'Rules'[Cover] = 'Case table'[Cover]
                && 'Rules'[Customer] = 'Case Table'[Customer_A]
        )
    )
VAR action_A =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_a] = "Less than",
                    'Case table'[Nr_action_A] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_A] = "More than",
                    'Case table'[Nr_action_A] > 'Rules'[ft_number]
                )
            )
        )
    )
VAR action_B =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_B] = "Less than",
                    'Case table'[Nr_action_B] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_B] = "More than",
                    'Case table'[Nr_action_B] > 'Rules'[ft_number]
                )
            )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Fee] IN t_customer
                && 'Rules'[Fee] IN action_A
                && 'Rules'[Fee] IN action_B
        )
    )

By the way, PBIX file as attached.

Best regards,

[parry2k]

@setis please explain the logic, it is not fully clear to me what you are trying to achieve.

[setis]

@parry2k , I'm sorry if I wasn't clear enough. 

I am trying to create a column in my Case table (the table below in the screenshot)  that identifies the Fee that the case should have based on the Fee rules table (the above table).

Case1 has <1 Nr_action_A and <5 Nr_Action_B therefore its Fee is "Fee_Gold"

Does this make sense?

[parry2k]

@setis what are the columns we are checking in rules table.

[setis]

All except amount. 

As you can see in the measure:

Column = 
CALCULATE(MAX('Fee Rules'[Fee]);
FILTER('Fee Rules';
'Fee Rules'[Customer] = Case_table[Customer]
&& 'Fee Rules'[Case Type] = Case_table[Case_type]
&& 'Fee Rules'[Cover] = Case_table[Cover]))

The Customer, Case Type and Cover must be the same. The issue is Nr_Action_A and Nr_Action_B that is "More than" or "Less than" the value of the column "ft_number". 

[setis]

@parry2k ,

As an attempt to make it more clear I added a couple of calculated columns:

As you can see what I'm looking for in the desired result column is something like: Check the Nr_action_A and i need to filter the Fee rules for the rows that match Nr_action_A with the Action_A_operator and Nr_actionA from Fee rules.  For Case 1 Nr-action_A = 0 so it would return from fee rules the second row that is Action A = Less than 1.

Does this make sense? Is this possible?

[v-lid-msft · Accepted solution]

Hi @setis ,

We can try to create a caluclated column using following formula to meet your requirement:

Fee (desired result) = 
VAR t_customer =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Case_Type] = 'Case table'[Case_Type]
                && 'Rules'[Cover] = 'Case table'[Cover]
                && 'Rules'[Customer] = 'Case Table'[Customer_A]
        )
    )
VAR action_A =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_a] = "Less than",
                    'Case table'[Nr_action_A] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_A] = "More than",
                    'Case table'[Nr_action_A] > 'Rules'[ft_number]
                )
            )
        )
    )
VAR action_B =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_B] = "Less than",
                    'Case table'[Nr_action_B] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_B] = "More than",
                    'Case table'[Nr_action_B] > 'Rules'[ft_number]
                )
            )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Fee] IN t_customer
                && 'Rules'[Fee] IN action_A
                && 'Rules'[Fee] IN action_B
        )
    )

By the way, PBIX file as attached.

Best regards,

[setis]

@v-lid-msft thank you SO MUCH! 

[setis]

Dear @v-lid-msft , thanks again for your help so far

The logic of the calculate column is not working when there are blanks. 

I have added to your file a new case and 2 rules that produce wrong results on a calculated column "Fee2" that I created trying to take the blanks into consideration. 

Could you please look into it?

https://drive.google.com/file/d/1ZHID2gptlonyyA83albNwiGHxnDueRy2/view?usp=sharing 

PS. How do I attach a file to the posts?

[v-lid-msft]

Hi @setis ,

We have found our mistake in the formula, please try to use the following measure and verify if the output is proper:

Fee (desired result) = 
VAR t_customer =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Case_Type] = 'Case table'[Case_Type]
                && 'Rules'[Cover] = 'Case table'[Cover]
                && 'Rules'[Customer] = 'Case Table'[Customer_A]
        )
    )
VAR action_A =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_a] = "Less than",
                    'Case table'[Nr_action_A] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_A] = "More than",
                    'Case table'[Nr_action_A] > 'Rules'[ft_number]
                )
            )
        )
    )
VAR action_B =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            OR (
                AND (
                    'Rules'[Nr_action_B] = "Less than",
                    'Case table'[Nr_action_B] < 'Rules'[ft_number]
                ),
                AND (
                    'Rules'[Nr_action_B] = "More than",
                    'Case table'[Nr_action_B] > 'Rules'[ft_number]
                )
            )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Fee] IN t_customer
                && 'Rules'[Fee] IN action_A
                && 'Rules'[Fee] IN action_B
        )
    )

By the way, PBIX file as attached.

Best regards,

[setis]

Dear @v-lid-msft ,

Thanks a lot. This is great!

With the information in the example, it works perfectly. However, in my real life report, there can be cases whose conditions match the rules of 2 or more fees. 

Like case 4 here:

cases:

Rules:

How can I implement the logic in the column that when this happens, it has to choose the fee with the highest amount?

I tried adding a second filter at the end, but it didn't work:

Fee = 
VAR t_customer =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] );
        FILTER (
            'Rules';
            'Rules'[Case_Type] = 'Case table'[Case_Type]
                && 'Rules'[Cover] = 'Case table'[Cover]
                && 'Rules'[Customer] = 'Case Table'[Customer_A]
        )
    )
VAR action_A =
    CALCULATETABLE (
        DISTINCT ( Rules[Fee] );
        FILTER (
            'Rules';
            AND (
                'Rules'[Nr_action_A] = "Less than";
                'Case Table'[Nr_action_A] < Rules[ft_number]
            )
                || AND (
                    'Rules'[Nr_action_A] = "More than";
                    'Case Table'[Nr_action_A] > Rules[ft_number]
                )
                ||  AND ('Rules'[Nr_action_A] & "" = ""; ISBLANK('Case Table'[Nr_action_A] ))
        )
    )
VAR action_B =
    CALCULATETABLE (
        DISTINCT ( Rules[Fee] );
        FILTER (
            'Rules';
            AND (
                'Rules'[Nr_action_B] = "Less than";
                'Case Table'[Nr_action_B] < Rules[ft_number]
            )
                || AND (
                    'Rules'[Nr_action_B] = "More than";
                    'Case Table'[Nr_action_B] > Rules[ft_number]
                )
                ||AND ( 'Rules'[Nr_action_B] & "" = ""; ISBLANK('Case Table'[Nr_action_B] ))
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Rules'[Fee] );
        FILTER (
            'Rules';
            'Rules'[Fee] IN t_customer
                && 'Rules'[Fee] IN action_A
                && 'Rules'[Fee] IN action_B
        );
        FILTER(Rules;MAX(Rules[Amount]))
    )

The file is here: https://drive.google.com/file/d/1ZHID2gptlonyyA83albNwiGHxnDueRy2/view?usp=sharing

Thanks in advance!

[v-lid-msft · Accepted solution]

Hi @setis ,

We can use the following measure to meet your requirement:

Fee =
VAR t_customer =
    CALCULATETABLE (
        DISTINCT ( 'Rules'[Fee] ),
        FILTER (
            'Rules',
            'Rules'[Case_Type] = 'Case table'[Case_Type]
                && 'Rules'[Cover] = 'Case table'[Cover]
                && 'Rules'[Customer] = 'Case Table'[Customer_A]
        )
    )
VAR action_A =
    CALCULATETABLE (
        DISTINCT ( Rules[Fee] ),
        FILTER (
            'Rules',
            AND (
                'Rules'[Fee] IN t_customer,
                AND (
                    'Rules'[Nr_action_A] = "Less than",
                    'Case Table'[Nr_action_A] < Rules[ft_number]
                )
                    || AND (
                        'Rules'[Nr_action_A] = "More than",
                        'Case Table'[Nr_action_A] > Rules[ft_number]
                    )
                    || AND ( 'Rules'[Nr_action_A] & "" = "", ISBLANK ( 'Case Table'[Nr_action_A] ) )
            )
        )
    )
VAR action_B =
    CALCULATETABLE (
        DISTINCT ( Rules[Fee] ),
        FILTER (
            'Rules',
            AND (
                'Rules'[Fee] IN t_customer,
                AND (
                    'Rules'[Nr_action_B] = "Less than",
                    'Case Table'[Nr_action_B] < Rules[ft_number]
                )
                    || AND (
                        'Rules'[Nr_action_B] = "More than",
                        'Case Table'[Nr_action_B] > Rules[ft_number]
                    )
                    || AND ( 'Rules'[Nr_action_B] & "" = "", ISBLANK ( 'Case Table'[Nr_action_B] ) )
            )
        )
    )
VAR temp =
    FILTER (
        SUMMARIZE (
            FILTER ( 'Rules', 'Rules'[Fee] IN t_customer ),
            Rules[Fee],
            "RulesNumber", COUNTROWS ( Rules ),
            "TotalAmount", SUM ( Rules[Amount] )
        ),
        IF (
            [RulesNumber] = 1,
            'Rules'[Fee] IN action_A
                || 'Rules'[Fee] IN action_B,
            'Rules'[Fee] IN action_A
                && 'Rules'[Fee] IN action_B
        )
    )
RETURN
    MAXX ( TOPN ( 1, temp, [TotalAmount], DESC ), [Fee] )

By the way, PBIX file as attached.

Best regards,

[setis]

@v-lid-msft It seems to be working. I can't thank you enough for your help here 🙂