Create a calculatetable excluding X-Axis value

[Vincem35]

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

[Icey]

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

[Icey]

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.