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 mod...
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)
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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.