Conditions in measure

[DmitryD77]

Hi Community.

I need to classify customers by conditions. I have a DB with a Direct Query connection and conditions in two tables that are loaded locally (from SharePoint). I can't use import mode and can't do the required transformation in Power Query. So, I have a mixed model and I need the measure that will be calculated based on two conditions.

What is needed in the first condition:

IF (
DQ - Sample Table [Customer] = LD - First Conditions [Customer]
AND
DQ - Sample Table [Channel] = LD - First Conditions [Channel]
AND
[Value by Customer] >= LD - First Conditions [Value]
THEN
First Conditions [Type])

What is needed in the second condition:

IF (
DQ - Sample Table [Customer] = LD - Second Conditions [Customer]
AND
DQ - Sample Table [Period] = LD - Secondt Conditions [Period]
AND
[Value by Customer] >= LD - Second Conditions [Value]
THEN
Second Conditions [Type])

Only this order of conditions is important.

I can't find a correct way to write the measure. Please help, hope it possible in DAX 🙂

All details in the file: join.pbix

PS And value conditions, as usual, must be calculated from highest to lowest values in each group.

[MFelix]

Try the following two measures:

Classification type 1 = 
VAR measurecalc = [Value by Customer]
VAR temptable =
    CALCULATETABLE (
        'LD - First Conditions',
        'LD - First Conditions'[Customer] IN VALUES ( 'DQ - Sample Table'[Customers] ),
        'LD - First Conditions'[Channel] IN VALUES ( 'DQ - Sample Table'[Channel] ),
        'LD - First Conditions'[Target Value] <= measurecalc
    )
RETURN
    MAXX ( temptable, 'LD - First Conditions'[Type] )

Classification type 2 = 
VAR measurecalc = [Value by Customer]
VAR temptable =
    CALCULATETABLE (
        'LD - Second Conditions',
         'LD - Second Conditions'[Customer] IN VALUES ( 'DQ - Sample Table'[Customers] ),
        'LD - Second Conditions'[Period] IN VALUES ( 'LD - Second Conditions'[Period] ),
        'LD - Second Conditions'[Target Value] <= measurecalc
    )
RETURN
    MAXX ( temptable, 'LD - Second Conditions'[Type] )

[DmitryD77]

Thanks, but one measure is needed for both conditions.

[MFelix]

How do you solve which one is selected?

If you have two categories for the same customer?

Do you want to show for example T1 / T2? 

[DmitryD77]

Hi, sorry for the misunderstanding. Classification of customers should be done in two stages, the calculations in the second stage should exclude the source data of the first condition.

[MFelix · Accepted solution]

Hi @DmitryD77 ,

Try the following code:

Classification type 1 = 
VAR measurecalc = [Value by Customer]
VAR ClassificationType1 =
    CALCULATETABLE (
        'LD - First Conditions',
        'LD - First Conditions'[Customer] IN VALUES ( 'DQ - Sample Table'[Customers] ),
        'LD - First Conditions'[Channel] IN VALUES ( 'DQ - Sample Table'[Channel] ),
        'LD - First Conditions'[Target Value] <= measurecalc
    )
var FinalResult1 =  MAXX ( ClassificationType1, 'LD - First Conditions'[Type] )

VAR ClassificationType2 =
    CALCULATETABLE (
        'LD - Second Conditions',
         'LD - Second Conditions'[Customer] IN VALUES ( 'DQ - Sample Table'[Customers] ),
        'LD - Second Conditions'[Period] IN VALUES ( 'LD - Second Conditions'[Period] ),
        'LD - Second Conditions'[Target Value] <= measurecalc
    )
var FinalResult2 =
    MAXX ( ClassificationType2, 'LD - Second Conditions'[Type] )



RETURN
   COALESCE(FinalResult1, FinalResult2)

[DmitryD77]

Hello,

sorry for a long reply. Unfortunately, the new measure doesn't work correctly. See file.

[MFelix]

Hi @DmitryD77 ,

Be aware that using the Type from the tables of first and second conditions on tables where you are using the Classification measure will result in error because of the context, since the type is part of the calculation it filters the table in a way that the only value returned is the same has the type for that line.

You need to use the classificitaion based on the DQ sample values:

As you can see the highlited values are the ones that are getting picked up. Is this incorrect in terms of the final result?

The ones that don't have classification is because they do not achieve the targets values.

Can you please elaborate more on what is incorrect in terms of result?

[DmitryD77]

Good day @MFelix ,

Sorry for the inconvenience, your solution worked perfectly on sample data, but on work DB it gives an error: "the result of a query to external DB has exceed max size of 1 Mio rows" (both in desktop version and in Power BI Service). Could you please help me with what I need to do to solve the issue?

Thank you in advance.

[MFelix]

This is related with performance and the calculations being done on a measure instead of having this has a column or similar on your model.

Did you tried making some filter to check if the formula works?

[DmitryD77]

Yes, even with filters, the same issue.

[MFelix]

Are the tables with the information about the classification of the customers very large?

[DmitryD77]

In 3 tables 2K, 5.6 mio and 7.4 mio rows.

[DmitryD77]

Yep, my mistake. Thank you very much, much appreciated!