Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Carlotta_Fabris
Frequent Visitor

Sum count of rows ignoring row context

Hi everyone,

 

I have two tables, one in which I have different response times, the other contains the different possible bins.

I have a relation between the two, the bin table filters the responses table (1-N).

 

I want to get the number of responses that are in a specific bin that is multiple of 5. For example for the bin that goes from 00:00:00 to 00:00:05 I have 168 responses.

 

I tried building a table with the bins in the rows and a simple count as the value, but it doesn't work.
It gets me the count of all the different bins, but I would like to have only the bins that are multiple of 5.

I tried filtering the bins but it doesn't work.

var _BinSize_Start = SELECTEDVALUE(Bins[Value])
var _BinSize_End = (_BinSize_Start + 'Bin Size'[Bin Size Value]) -1
var _ResponsesCount = COUNT(Data[Response Seconds])
var _ResponsesNr = 
CALCULATE(COUNT(Data[Response Seconds]), FILTER(ALL(Data[Response Seconds]), Data[Response Seconds] >= _BinSize_Start && Data[Response Seconds] <= _BinSize_End))
Return _ResponsesNr

Ideally I would like to sum the count of the rows that are between the start and end of the bins.Capture1.PNG

 

Any suggestions?

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @Carlotta_Fabris ,

 

I suggest you to create a DimBin table to help your calculation.

DimBin(time) = ADDCOLUMNS(VALUES('Table'[Bins (time)]),"Rank",RANKX(VALUES('Table'[Bins (time)]),[Bins (time)],,ASC))
Bins_Filter_Relation = 
VAR _IF =
    ADDCOLUMNS (
        'DimBin(time)',
        "If",
            VAR _Bin =
                MINX (
                    FILTER (
                        'DimBin(time)',
                        MOD ( 'DimBin(time)'[Rank], 5 ) = 1
                            && 'DimBin(time)'[Rank] >= EARLIER ( 'DimBin(time)'[Rank] )
                    ),
                    [Rank]
                )
            RETURN
                IF ( _Bin = BLANK (), MAX ( 'DimBin(time)'[Rank] ), _Bin )
    )
VAR _RANKX =
    ADDCOLUMNS ( _IF, "Bin", RANKX ( _IF, [If],, ASC, DENSE ) )
RETURN
SUMX (
        FILTER ( _RANKX, [Bins (time)] = EARLIER ( 'DimBin(time)'[Bins (time)] ) ),
        [Bin]
    )

Relationship:

vrzhoumsft_0-1709017330126.png

Result is as below.

vrzhoumsft_1-1709017486982.png

vrzhoumsft_2-1709017506303.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you for your answer, but I would like to have the total of the different bins that are multiple of 5 in one row of the matrix.

So for example on the row 00:00:00 I would like to see 168, then on the row 00:00:05 220 and so on.

This solution would also be difficult to replicate because then the bin size is dynamic and it changes form 5 to 10 to 15 to 20 etc.

123abc
Community Champion
Community Champion

It seems like you want to count the number of responses within bins that are multiples of 5. To achieve this, you can create a measure that calculates the count of responses within the specified bins.

Here's a revised version of your DAX measure:

 

ResponsesCountMultipleOf5 =
VAR BinStart = SELECTEDVALUE(Bins[Value])
VAR BinEnd = BinStart + SELECTEDVALUE('Bin Size'[Bin Size Value]) - 1
VAR BinMultipleOf5 = TRUNC(BinStart / 5) * 5
VAR ResponsesCount =
CALCULATE(
COUNT(Data[Response Seconds]),
FILTER(
ALL(Data),
Data[Response Seconds] >= BinStart &&
Data[Response Seconds] <= BinEnd
)
)
RETURN
IF(MOD(BinMultipleOf5, 5) = 0, ResponsesCount, BLANK())

 

Explanation:

  1. BinStart and BinEnd are calculated based on the selected bin and its size.
  2. BinMultipleOf5 calculates the nearest multiple of 5 for the bin start time.
  3. ResponsesCount calculates the count of responses within the specified bin range.
  4. The IF statement checks if the bin is a multiple of 5. If it is, it returns the ResponsesCount; otherwise, it returns BLANK().

This measure should give you the count of responses within bins that are multiples of 5. Make sure to use this measure in your visualizations to see the desired counts.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Thank you!

 

This worked, but now I can't manage to get the correct total.

VAR BinStart = SELECTEDVALUE(Bins[Value])
VAR BinEnd = BinStart + SELECTEDVALUE('Bin Size'[Bin Size]) - 1
VAR BinMultipleOfSize = TRUNC(BinStart / 'Bin Size'[Bin Size Value]) * 'Bin Size'[Bin Size Value]
VAR ResponsesCount =
        CALCULATE(
            COUNT(Data[Response Seconds]),
            FILTER(
                ALL(Data),
                Data[Response Seconds] >= BinStart &&
                Data[Response Seconds] <= BinEnd
            )
        )
RETURN
IF(ISINSCOPE(Bins[Bins (time)]),
    IF(MOD(BinMultipleOfSize, 'Bin Size'[Bin Size Value]) = 0, ResponsesCount, BLANK()),
    CALCULATE(COUNT(Data[Response Seconds]),REMOVEFILTERS(Data[Response Seconds]))
)
 
I tried like this, but it doesn't work.
Just to give you some more context, I was trying to improve the performances of the matrix but this does't seem to solve the problem. IF you want to read something more on this, read my other post https://community.fabric.microsoft.com/t5/Desktop/Improve-Performances-on-matrix-and-histogram/m-p/3...
 
Thank you again,
Carlotta.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors