Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

logical error in dynamic filtering for calculated tables using summarize

Hi All  I have created a measure using summarize which dynamically calculates a list for last year and this works fine for all the filters. Still, i  am unable to filter it dynamically using the c...
DAX
SUMMARIZE
  • Anonymous's avatar
    Anonymous
    1 year ago

    HI Anonymous 

    Apologies for the late reply , but i have magaed to resolve it myself.

    The issue with the code was regardig data lineage calculated columns. 
    work around was to created indipendant calculated coumns for the selections. 
    Please find the updated code below . 

    var Start_Date = FIRSTNONBLANK( 'Trading Days'[Date] , 'Trading Days'[Date] )
    var start_Date_LY =  DATEADD( Start_Date , -1 ,  YEAR)

    var End_Date =   LASTNONBLANK( 'Trading Days'[Date] , 'Trading Days'[Date] )
    var End_Date_LY = DATEADD( End_Date , -1 ,  YEAR)

    var Selected_Segment = SELECTEDVALUE( 'Customer Segmentation'[ID] , 0 )

     VAR My_table =
                   SUMMARIZE ( Append1 ,
           
            Append1[CustomerList_Filtered],
            "Accepted Jobs2", [Accepted Jobs.AG],
            "Accepted Revenue2", [Accepted Sales.AG]
        )
       
                               
    VAR final_IC =  FILTER ( My_table, [Accepted Jobs2] = 1  && [Accepted Revenue2] <= 3000 &&   Append1[CustomerList_Filtered] <> "NO NAME" )

    VAR final_MSC =  FILTER ( My_table, [Accepted Jobs2] > 1 && [Accepted Revenue2] <= 3000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

    VAR final_MC =FILTER ( My_table,  [Accepted Revenue2] > 3000 && [Accepted Revenue2] <= 12000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

    VAR final_KC =  FILTER ( My_table,   [Accepted Revenue2] > 12000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

    return
    IF( Selected_Segment = 1 , SUMX(  final_IC , [Accepted Revenue2])  ,
    IF( Selected_Segment = 2 ,  SUMX( final_MSC , [Accepted Revenue2]) ,
    IF( Selected_Segment = 3 ,  SUMX( final_MC , [Accepted Revenue2]) ,
    IF( Selected_Segment = 4 ,  SUMX( final_KC , [Accepted Revenue2]) ,
    IF( Selected_Segment = 5 , SUMX( final_MSC , [Accepted Revenue2])+SUMX( final_MC , [Accepted Revenue2])+SUMX( final_KC , [Accepted Revenue2]) ,
    SUMX( My_table , [Accepted Revenue2]  ) )
    ))))


    FYI everything is based on only one fact table and there is no join between the fact table(Append1) and the refrence table(Customer Segmentation). 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous 
can you please provide sample data?
Thank you

Anonymous's avatar
Anonymous
Not applicable
1 year ago

HI Anonymous 

Apologies for the late reply , but i have magaed to resolve it myself.

The issue with the code was regardig data lineage calculated columns. 
work around was to created indipendant calculated coumns for the selections. 
Please find the updated code below . 

var Start_Date = FIRSTNONBLANK( 'Trading Days'[Date] , 'Trading Days'[Date] )
var start_Date_LY =  DATEADD( Start_Date , -1 ,  YEAR)

var End_Date =   LASTNONBLANK( 'Trading Days'[Date] , 'Trading Days'[Date] )
var End_Date_LY = DATEADD( End_Date , -1 ,  YEAR)

var Selected_Segment = SELECTEDVALUE( 'Customer Segmentation'[ID] , 0 )

 VAR My_table =
               SUMMARIZE ( Append1 ,
       
        Append1[CustomerList_Filtered],
        "Accepted Jobs2", [Accepted Jobs.AG],
        "Accepted Revenue2", [Accepted Sales.AG]
    )
   
                           
VAR final_IC =  FILTER ( My_table, [Accepted Jobs2] = 1  && [Accepted Revenue2] <= 3000 &&   Append1[CustomerList_Filtered] <> "NO NAME" )

VAR final_MSC =  FILTER ( My_table, [Accepted Jobs2] > 1 && [Accepted Revenue2] <= 3000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

VAR final_MC =FILTER ( My_table,  [Accepted Revenue2] > 3000 && [Accepted Revenue2] <= 12000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

VAR final_KC =  FILTER ( My_table,   [Accepted Revenue2] > 12000 &&  Append1[CustomerList_Filtered] <> "NO NAME" )

return
IF( Selected_Segment = 1 , SUMX(  final_IC , [Accepted Revenue2])  ,
IF( Selected_Segment = 2 ,  SUMX( final_MSC , [Accepted Revenue2]) ,
IF( Selected_Segment = 3 ,  SUMX( final_MC , [Accepted Revenue2]) ,
IF( Selected_Segment = 4 ,  SUMX( final_KC , [Accepted Revenue2]) ,
IF( Selected_Segment = 5 , SUMX( final_MSC , [Accepted Revenue2])+SUMX( final_MC , [Accepted Revenue2])+SUMX( final_KC , [Accepted Revenue2]) ,
SUMX( My_table , [Accepted Revenue2]  ) )
))))


FYI everything is based on only one fact table and there is no join between the fact table(Append1) and the refrence table(Customer Segmentation).