Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditions in measure

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.

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    Hi Anonymous ,

     

    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)

13 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks, but one measure is needed for both conditions.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        3 years ago

        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?