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 
We haven’t heard back since our last response and wanted to check if your query has been resolved. If not, please feel free to reach out for further assistance. If it has been resolved, kindly mark the helpful reply as the solution to make it easier for others to find. A kudos would also be greatly appreciated!
Thank you.