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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,

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

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

      Hi Anonymous ,

       

      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?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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