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). 

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
1 year ago

Hi ,Anonymous 

Since the measure accept a scalar value and the above code you have provided returns a table, the code you supplied above is unacceptable in the measure. Could you please supply the entire measure code?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Dangar332 

    please find below the full code


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

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

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


     VAR My_table =
                   SUMMARIZE (
            FILTER (
                ALLEXCEPT(  Append1 ,
                        Append1[Service Centre Name],
                        Append1[Channel V1] ,
                        Append1[NSO] ,
                        Append1[Month No] ,
                        Append1[CustomerOrganisation]
                     
                   
                ) ,
                Append1[StatusDateTime] >= Start_Date &&
                Append1[StatusDateTime] <= End_Date
            )
                ,
            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]  ) )
    ))))