Incorrect filtering of the summarized table

[Anonymous]

Hi all,

I have been looking up similar posts but I could not really get what I needed, so I post the question here and hope to get some help.

I have 1) factTable and 2) LengthBin tables that looks like the following, in which factTable[LengthBinned] is connected to LengthBin[Value]:

   

What I want to do is to summarize the factTable by person ID to get the sum of length of each individual, and count the number of occurences per each length bin (from 0 to 10, if the sum of length is greater than 10, then I want to group it to 10), i.e. I want to have a table like the following:

CountSumOfLength = 
-- the maximum length i.e. 10 in this example
var MaxLength = max(LengthBin[Value])

-- table to summarize for each individual guest
var _table = 
ADDCOLUMNS(
    SUMMARIZE(factTable, factTable[PersonId],
    "SumOfLength", sum(factTable[Length])),
    "SumOfLengthBin", if([SumOfLength]<=0, 0, if([SumOfLength]>=MaxLength,MaxLength ,[SumOfLength]))
    )

-- table to summarize each length bin
var _table2 = 
    SUMMARIZE(_table,[SumOfLengthBin],
    "Frequency", COUNTROWS(_table)
    )


return 
maxx(_table2,Frequency) + 0

This DAX returns something, but since the factTable[LengthBinned] is connected to LengthBin[Value], if I select a random value of LengthBin, say 1, then the calculation will return the rows in the factTable that have a length of 1, instead of persons with the total length that equals 1.

So my question is: Is there a way to fix the relationship so that the LengthBin[Value] could refer to the SUM of length, instead of each individual row in the factTable? (I tried different methods, such as TREATAS, INTERSECT, but since _table is a table variable, the methods do not seem to work)

Or if you have suggestions on how can I achieve this, I am happy to be enlightened! Thanks in advance.

[v-juanli-msft]

Hi @Anonymous 

To show the count of occurences per each length bin based on slicers, you could create a measure

(Note! there is no relationship between these two tables)

Measure 3 =
VAR _table =
    SUMMARIZE (
        GENERATE ( LengthBin, factTable ),
        [Value],
        [PersonID],
        "sum_length", IF (
            CALCULATE ( SUM ( factTable[Length] ) ) >= 10,
            10,
            CALCULATE ( SUM ( factTable[Length] ) )
        )
    )
RETURN
    COUNTAX ( FILTER ( _table, [sum_length] = [Value] ), [PersonID] )

2. To show the rows based on the value of LengthBin, create a measure and add this measure to the visual level filter of the table visual.

sum of length = CALCULATE(SUM(factTable[Length]),FILTER(ALLSELECTED(factTable),factTable[PersonID]=MAX(factTable[PersonID])))

sum of length adjusted = IF([sum of length]>=10,10,[sum of length])

filter flag = IF([sum of length adjusted]=SELECTEDVALUE(LengthBin[Value])||NOT(ISFILTERED(LengthBin[Value])),1,0)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

Many thanks, it works! Just one more question before accepting, i notice starting from around 50k rows, visuals take quite a long time to display. Is there a way to make the calculation faster? As I see right now no table relationships are made.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Modify the measures ad below:

Measure 3 = 
VAR _table =
    SUMMARIZE (
        GENERATE ( LengthBin, factTable ),
        [Value],
        [PersonID], 
        "sum_length", var sum_=CALCULATE ( SUM ( factTable[Length] ) ) return IF (
            sum_ >= 10,
            10,
            sum_ )
        )
RETURN
    COUNTAX ( FILTER ( _table, [sum_length] = [Value] ), [PersonID] )


sum of length adjusted =
VAR sum_of_length =
    CALCULATE (
        SUM ( factTable[Length] ),
        FILTER (
            ALLSELECTED ( factTable ),
            factTable[PersonID] = MAX ( factTable[PersonID] )
        )
    )
RETURN
    IF ( sum_of_length >= 10, 10, sum_of_length )


filter flag =
IF (
    [sum of length adjusted] = SELECTEDVALUE ( LengthBin[Value] )
        || NOT ( ISFILTERED ( LengthBin[Value] ) ),
    1,
    0
)

With these measures, the performance on my side improve.

with new measures:

With old measures

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks maggie, it works like a charm!

[v-juanli-msft]

Hi @Anonymous 

Do you need any slicers to filter the factTable and to show different "sum of length" per selection?

Could you accept to create calculated columns for the  "sum of length"?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

Thanks for your reply. The example I provided here is a simplified version, but at the end I do want to have a date slicer so users can select a particular period and get the frequency of sum of length.

Regards,

Anson