Forum Discussion

Vincem35's avatar
Vincem35
Icon for Helper I rankHelper I
5 years ago

Create a calculatetable excluding X-Axis value

Dear Community, 

 

I am trying to generate a calculated Table to filter my clients based on their first transactions and display this information on a chart below

 

I am having an issue with the X-Axis = Client Typology which is filtering the calculated table even if i ask the table to avoid the field
below is my Dax

 

M1 = 
Var Min_Date = "2019-01-01"
Var Max_Date = "2019-12-31"

var Table_Temp1 = 
    CALCULATETABLE(
    SUMMARIZE('POS Boutique Transactions',
    'POS Boutique Transactions'[Client_ID],
    "Min. Transaction", [Min. Transaction]),
    'POS Boutique Transactions'[REGISTERED CLIENT] = true(),
    filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_VALID_TO]> Max_Date),
    filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_DOCUMENT] >= Min_Date && 'POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date))

 Var Table_Temp = CALCULATETABLE(Table_Temp1, all('POS Boutique Transactions'[CLIENT TYPOLOGY]))

var Cal = 
    CALCULATE(
    SUMX(DISTINCT('POS Boutique Transactions'[Client_ID]),1),
    TREATAS(Table_Temp,'POS Boutique Transactions'[Client_ID], 'POS Boutique Transactions'[DT_DOCUMENT]),
    filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_VALID_TO]> Max_Date),
    filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_DOCUMENT] >= Min_Date && 'POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date))
    
var Test = if(isblank(Cal), 0, if(Cal>0, Cal))
Return Test

 

Thanks for your help

 

Need Help

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Vincem35 ,

     

    How about this?

    M1 =
VAR Min_Date = "2019-01-01"
VAR Max_Date = "2019-12-31"
VAR Table_Temp =
    CALCULATETABLE (
        SUMMARIZE (
            'POS Boutique Transactions',
            'POS Boutique Transactions'[Client_ID],
            "Min. Transaction", [Min. Transaction]
        ),
        'POS Boutique Transactions'[REGISTERED CLIENT] = TRUE (),
        ALL ( 'POS Boutique Transactions'[CLIENT TYPOLOGY] ),
        //              added          //
        FILTER (
            'POS Boutique Transactions',
            'POS Boutique Transactions'[DT_VALID_TO] > Max_Date
        ),
        FILTER (
            'POS Boutique Transactions',
            'POS Boutique Transactions'[DT_DOCUMENT] >= Min_Date
                && 'POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date
        )
    )
VAR Cal =
    CALCULATE (
        SUMX ( DISTINCT ( 'POS Boutique Transactions'[Client_ID] ), 1 ),
        TREATAS (
            Table_Temp,
            'POS Boutique Transactions'[Client_ID],
            'POS Boutique Transactions'[DT_DOCUMENT]
        ),
        FILTER (
            'POS Boutique Transactions',
            'POS Boutique Transactions'[DT_VALID_TO] > Max_Date
        ),
        FILTER (
            'POS Boutique Transactions',
            'POS Boutique Transactions'[DT_DOCUMENT] >= Min_Date
                && 'POS Boutique Transactions'[DT_DOCUMENT] <= Max_Date
        )
    )
VAR Test =
    IF ( ISBLANK ( Cal ), 0, IF ( Cal > 0, Cal ) )
RETURN
    Test

     

    If this doesn't work, it is suggested to share us a dummy .pbix file with the same issue. Please remove sensitive information.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Vincem35 ,

     

    I am curious if you have solved this problem.

     

    If it is solved, can you share your solution with us? Or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If not, please let us know.

     

     

    Best Regards,

    Icey