topic Re: SUM rows filtered by data segmentation in DAX Commands and Tips

SUM rows filtered by data segmentation

Anonymous — Mon, 14 Mar 2022 14:59:48 GMT

Hello everyone,
I'm trying to SUM a column called "Offline Time". This column has 300 rows. But I don't want to SUM all these rows, I want to SUM using data segmentation as a parameter.
For example, I select 1 week, in data segmentation, this means that I need to SUM only seven rows in "Offline Time" and not all 300 rows.
Thanks in advance.

Re: SUM rows filtered by data segmentation

Whitewater100 — Mon, 14 Mar 2022 16:01:20 GMT

Hi:

If you have a column for week no. in your fact table and you have built your slicer off of that, your measure should work dynamically.

The measure would be something like Total = SUM(TableName[Offline Time)

It will give you the correct answer if your column next to Offline Time has week no.

Here is calculated column for week no:

Week No = WEEKNUM(TableName[Date Field])

If this doesn't work can you post some more info about your question? (The model, what the table has for columns, relationships,..)

Thanks

Re: SUM rows filtered by data segmentation

Anonymous — Mon, 14 Mar 2022 18:04:58 GMT

Thanks for the answer 😉

But I believe that the key is to sum the column with some condition.
I'm trying something like that:

SUM Offline time = CALCULATE(SUMX(Offline, Offline[Offline time(minutes)]), ALLSELECTED(Offline[Alert Date]))

I'm not sure if I should use ALLSELECTED. What I intend to do is to sum the Offline Time(minutes) using the offline as a parameter. Notice that in data segmentation I'm using Calendar Offline[Date] instead of Offline[Alert Date]

Re: SUM rows filtered by data segmentation

Whitewater100 — Mon, 14 Mar 2022 19:09:50 GMT

Hi:

If I can see the data (some data with exepcted result) I can try to answer more thoruoghly.

The way yo have your data model and the visual you are attempting to create pretty much drives the DAX solution.

Thanks!

Re: SUM rows filtered by data segmentation

Anonymous — Mon, 14 Mar 2022 20:08:02 GMT

Table Offline Calendar:

Offline table:

Data Segmentation using Offline Calendar:

Now I need to sum these three offline time rows (Tempo Offline(min))

Thanks in advance 🙂

Re: SUM rows filtered by data segmentation

Icey — Thu, 17 Mar 2022 08:28:20 GMT

Hi Anonymous ,

Do you create relationship between your two tables? If you have created it, no other action is needed. The measure result will change dynamically based on your date slicer, which called "visual interactions".

For more details about relationship, please check:

Model relationships in Power BI Desktop - Power BI | Microsoft Docs

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

For more details about visual interactions, please check:

Slicers in Power BI - Power BI | Microsoft Docs

Change how visuals interact in a report - Power BI | Microsoft Docs

Understand how visuals interact in a report - Power BI | Microsoft Docs

Filters and highlighting in Power BI reports - Power BI | Microsoft Docs

Best Regards,

Icey

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

Re: SUM rows filtered by data segmentation

Whitewater100 — Thu, 17 Mar 2022 12:52:16 GMT

Hi:

In your date table, please mark column date as Date Table. Then add calc columns to date table.

WEEK No. = WEEKNUM(DateTableName[Date])

YEAR = YEAR(DateTableName[Date])

Month No. = MONTH(DateTableName[Date])

Connect your date table date filed to your facttable date column, to create a relationship.

Stay with original measure Total = SUM(Table[offlinetime])

Add slicer taking week no field from date table. picture below.

Use slicer to get the the values you want for sum of offlinetime.

Re: SUM rows filtered by data segmentation

Anonymous — Thu, 17 Mar 2022 13:15:31 GMT

Hello,

thanks for your tip.

However, I can not use the week as a slicer. I need to use the complete date. And the full date is not working as well. 😕

Re: SUM rows filtered by data segmentation

Whitewater100 — Thu, 17 Mar 2022 13:28:34 GMT

Hi:

That doesn't make too much sense to me as I can't see your model. Can you supply this file for a more complete answer?

Thanks

Re: SUM rows filtered by data segmentation

Anonymous — Thu, 17 Mar 2022 13:37:25 GMT

Of course not. This file is for my organization. lol

I'm not studying. I'm working.

Re: SUM rows filtered by data segmentation

Anonymous — Thu, 17 Mar 2022 14:13:16 GMT

Hello,

after your tip, I did the relationship between these two tables. But it does not help at all.

Re: SUM rows filtered by data segmentation

Icey — Tue, 22 Mar 2022 09:29:02 GMT

Hi Anonymous ,

Could you create a sample file with the same issue for test? You can simplify your model with some dummy data.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey