Forum Discussion

rrr's avatar
rrr
Frequent Visitor
4 years ago
Solved

Trying to access table variable fields in return command and count rows in the filtered table.

Hi Everyone,   Table template:   ID Product Amount 1 FOODS 2000 2 Fertilizer 5000 3 Livestock 5000 4 Unallocated 6000 5 FOODS 7000 3 Fertilizer 2000 7 Lives...
DAX
Table variables
  • rrr's avatar
    rrr
    4 years ago

    Hey Everyone,

     

    Thank you for taking time to work on the above issue.

     

    After spending some time I realized thtat the date slicer is filtering the rows involved in [SUM_AMT] variable calculation,  although it  is applied when defining the table in summarize function.

     

    This could be because when [SUM_AMT] variable is calculated, old DAX code is not referring to "tbl" variable instead its referrring to Leaderboard which is part of Data Model.

     

    To fix that ,I have modified formula to calculate [SUM_AMT] variable, adding cross filter to it. This solved the issue.

     

    Here is the updated solution for the same.

     

    Thanks

    RRR.

     

     

    Number of Persons in top 20% =
    VAR tbl =
    SUMMARIZE (
    CALCULATETABLE (
    FILTER (
    ALL ( Leaderboard ),
    Leaderboard[EOM] > [Date Start]
    && Leaderboard[EOM] <= [Date End]
    && LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED"
    ),
    CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
    ),
    'Leaderboard'[Employee ID],
    'Leaderboard'[Product Division],
    "SUM_AMT",
    CALCULATE (
    SUM ( 'Leaderboard'[GROSS_MARGIN_AMOUNT] ),
    CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
    )
    )
    VAR tbl2 =
    ADDCOLUMNS ( tbl, "rank", RANKX ( tbl, [SUM_AMT],, DESC, SKIP ) )
    VAR tbl3 =
    CALCULATETABLE (
    ADDCOLUMNS (
    tbl2,
    "Cum_Sum", SUMX ( TOPN ( [rank], tbl2, [sum_amt], DESC ), [Sum_AMt] )
    )
    )
    VAR tbl4 =
    CALCULATETABLE ( ADDCOLUMNS ( tbl3, "Total_Sum", SUMX ( tbl3, [SUM_AMT] ) ) )
    RETURN
    COUNTROWS ( FILTER ( tbl4, [CUm_SUm] <= 0.2 * [Total_Sum] ) )

     

     

rrr's avatar
rrr
Frequent Visitor
4 years ago

Hey Everyone,

 

Thank you for taking time to work on the above issue.

 

After spending some time I realized thtat the date slicer is filtering the rows involved in [SUM_AMT] variable calculation,  although it  is applied when defining the table in summarize function.

 

This could be because when [SUM_AMT] variable is calculated, old DAX code is not referring to "tbl" variable instead its referrring to Leaderboard which is part of Data Model.

 

To fix that ,I have modified formula to calculate [SUM_AMT] variable, adding cross filter to it. This solved the issue.

 

Here is the updated solution for the same.

 

Thanks

RRR.

 

 

Number of Persons in top 20% =
VAR tbl =
SUMMARIZE (
CALCULATETABLE (
FILTER (
ALL ( Leaderboard ),
Leaderboard[EOM] > [Date Start]
&& Leaderboard[EOM] <= [Date End]
&& LEFT ( Leaderboard[Name], 11 ) <> "UNALLOCATED"
),
CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
),
'Leaderboard'[Employee ID],
'Leaderboard'[Product Division],
"SUM_AMT",
CALCULATE (
SUM ( 'Leaderboard'[GROSS_MARGIN_AMOUNT] ),
CROSSFILTER ( Leaderboard[EOM], dimDate[EOMONTH_DATE], NONE )
)
)
VAR tbl2 =
ADDCOLUMNS ( tbl, "rank", RANKX ( tbl, [SUM_AMT],, DESC, SKIP ) )
VAR tbl3 =
CALCULATETABLE (
ADDCOLUMNS (
tbl2,
"Cum_Sum", SUMX ( TOPN ( [rank], tbl2, [sum_amt], DESC ), [Sum_AMt] )
)
)
VAR tbl4 =
CALCULATETABLE ( ADDCOLUMNS ( tbl3, "Total_Sum", SUMX ( tbl3, [SUM_AMT] ) ) )
RETURN
COUNTROWS ( FILTER ( tbl4, [CUm_SUm] <= 0.2 * [Total_Sum] ) )