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 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi 

Thank you for the reply, but I am trying to generate a dynamic virtual table that will react to the filters.