Solved: Re: Additional filter into measure

dkushner · ‎02-09-2018

Hello everybody,

I have a table of values

ID	CounterNumber	ReqDateT	DataDateT	SummAPlus
68511	139	2018-02-06 17:14:45.000	2018-02-05 00:00:00.000	11632,8
68512	139	2018-02-06 17:14:54.000	2018-02-05 00:00:00.000	11632,8
68529	149	2018-02-06 17:17:16.000	2018-02-05 00:00:00.000	1075
68530	149	2018-02-06 17:17:20.000	2018-02-05 00:00:00.000	1075
68599	1	2018-02-06 17:22:51.000	2018-02-05 00:00:00.000	4933,6
68600	1	2018-02-06 17:22:55.000	2018-02-05 00:00:00.000	4933,6
68629	139	2018-02-06 21:00:16.000	2018-02-05 00:00:00.000	11632,8
68638	149	2018-02-06 21:02:23.000	2018-02-05 00:00:00.000	1075
68673	1	2018-02-06 21:06:54.000	2018-02-05 00:00:00.000	4933,6
68159	139	2018-02-05 07:00:24.000	2018-02-04 00:00:00.000	14162,4
68168	149	2018-02-05 07:02:31.000	2018-02-04 00:00:00.000	926,8
68203	1	2018-02-05 07:07:43.000	2018-02-04 00:00:00.000	5631,6

I need to select max value of SummAPlus for the each of CounterNumber by DataDateT

I can do it by:

ConsumedEnergy D = Max(PowerCountersPreviousDay[SummAPlus])

sumdeltaD = SUMX(FILTER(ALLSELECTED(PowerCountersPreviousDay[DataDateT]),lastdate(PowerCountersPreviousDay[DataDateT])),PowerCountersPreviousDay[ConsumedEnergy D])

and now I need to summarize consumption by several CounterNumbers, and I don't understand how to do it.

I will appreciate any help. Thanks

v-piga-msft · ‎02-25-2018

Hi @dkushner,

From the description of your replay, you should create a calculated table first in the formula below to get the max value of SummAPlus for the each of CounterNumber by DataDateT.

Table =
SUMMARIZE (
    PowerCountersPreviousDay,
    PowerCountersPreviousDay[DataDateT],
    PowerCountersPreviousDay[CounterNumber],
    "Sumsingle", MAX ( 'PowerCountersPreviousDay'[SummAPlus] )
)

Then, you could the measure :

Bussiness Unit =
VAR count1sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 1 )
    )
VAR count139sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 139 )
    )
VAR count149sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 149 )
    )
RETURN
    count139sum + count1sum
        - count149sum

This picture of the result is below:

For more details, you could refer to this pbix file.

Hope it can help you!

Best Regards,

Cherry

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

View solution in original post

v-piga-msft · ‎02-12-2018

Hi @dkushner，

If I understand your requirement correctly, you could create group with New Group to summarize consumption by several CounterNumbers. As the picture below:

For how to create the group, you could refer to this article.

For more details about the result picture above, you could refer to my pbix file here.

Additionally, you could provide your expectant result demo that I could help you create the visual.

In your convenience, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

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

dkushner · ‎02-13-2018

Dear Cherry,

Thank you very much for your help, but grouping is not exactly what I need. Or may be I don’t understand how to use it in my case ))

Let me explain my task a little bit wider:

I have a table with metering values of power counters (now I am talking about the table PowerCountersPreviousDay)

In this table there are metering values for the previous period and this data are repeated several times, for example:

ID	CounterNumber	ReqDateT	DataDateT	SummAPlus	SummAMinus	SummRPlus	SummRMinus	TransformationCoef
69439	1	2018-02-10 11:07:13.000	2018-02-09 00:00:00.000	7526	-1	5651,6	-1	400
69498	1	2018-02-10 15:07:08.000	2018-02-09 00:00:00.000	7526	-1	5651,6	-1	400
69734	1	2018-02-11 01:08:14.000	2018-02-10 00:00:00.000	4370,8	-1	3270	-1	400
69837	1	2018-02-11 17:25:38.000	2018-02-10 00:00:00.000	4370,8	-1	3270	-1	400
69838	1	2018-02-11 17:25:42.000	2018-02-10 00:00:00.000	4370,8	-1	3270	-1	400
69955	1	2018-02-12 17:26:05.000	2018-02-11 00:00:00.000	6194,8	-1	5055,2	-1	400
69956	1	2018-02-12 17:26:09.000	2018-02-11 00:00:00.000	6194,8	-1	5055,2	-1	400
70029	1	2018-02-12 23:07:26.000	2018-02-11 00:00:00.000	6194,8	-1	5055,2	-1	400
70088	1	2018-02-13 02:06:09.000	2018-02-12 00:00:00.000	4372,8	-1	3777,6	-1	400
69395	139	2018-02-10 11:00:20.000	2018-02-09 00:00:00.000	15817,8	-1	7865,4	-1	600
69454	139	2018-02-10 15:00:20.000	2018-02-09 00:00:00.000	15817,8	-1	7865,4	-1	600
69513	139	2018-02-10 17:16:17.000	2018-02-09 00:00:00.000	15817,8	-1	7865,4	-1	600
69514	139	2018-02-10 17:16:26.000	2018-02-09 00:00:00.000	15817,8	-1	7865,4	-1	600
69690	139	2018-02-11 01:00:18.000	2018-02-10 00:00:00.000	14913,6	-1	7365	-1	600
69749	139	2018-02-11 17:17:17.000	2018-02-10 00:00:00.000	14913,6	-1	7365	-1	600
69750	139	2018-02-11 17:17:26.000	2018-02-10 00:00:00.000	14913,6	-1	7365	-1	600
69867	139	2018-02-12 17:17:39.000	2018-02-11 00:00:00.000	5952	-1	3001,2	-1	600
69868	139	2018-02-12 17:17:47.000	2018-02-11 00:00:00.000	5952	-1	3001,2	-1	600
69985	139	2018-02-12 23:00:20.000	2018-02-11 00:00:00.000	5952	-1	3001,2	-1	600
70044	139	2018-02-13 02:00:09.000	2018-02-12 00:00:00.000	3350,4	-1	1525,8	-1	600
69404	149	2018-02-10 11:02:26.000	2018-02-09 00:00:00.000	1068,6	-1	369,8	-1	100
69463	149	2018-02-10 15:02:25.000	2018-02-09 00:00:00.000	1068,6	-1	369,8	-1	100
69531	149	2018-02-10 17:18:49.000	2018-02-09 00:00:00.000	1068,6	-1	369,8	-1	100
69699	149	2018-02-11 01:02:33.000	2018-02-10 00:00:00.000	992,8	-1	357,9	-1	100
69767	149	2018-02-11 17:20:01.000	2018-02-10 00:00:00.000	992,8	-1	357,9	-1	100
69768	149	2018-02-11 17:20:05.000	2018-02-10 00:00:00.000	992,8	-1	357,9	-1	100
69885	149	2018-02-12 17:20:10.000	2018-02-11 00:00:00.000	978,2	-1	380,5	-1	100

I need to calculate a power consumption for the Business units, a formula looks like this = (SummAPlus of CounterNumber 1) +(SummAPlus of CounterNumber 139) - (SummAPlus of CounterNumber 149)

It is very easy for each specific day, but I need automatic scaling for month, year and so on.

For this purpose I need to select only 1 row from the table for each DataDateT

For more convenient investigation, my pbix file with data is here.

Best regards, Dmitry

v-piga-msft · ‎02-13-2018

Hi @dkushner，

From the details you provide, if I understand your requirement correctly, you could use Data Hierarchy to get the Business Units of Year, Month or Day. You could create the measure :

Business Units = 
var count1sum =CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=1)) 
var count139sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=139) ) 
var count149sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=149)) 
RETURN 
count139sum+count1sum-count149sum

You could refer to this picture below:

Reference for pbix file

Hope it can help you!

Best Regards,

Cherry

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

dkushner · ‎02-14-2018

Hi!

I am really sorry, but it isn't so easy )

as I mentioned, I have repeated rows in the same period, and your formula summarized all repeating values.

but I have to select only one. For example for the CounterNumber = 1,

I have 3 rows , with the same indications in the 1 day, I need to use in calculation only 1

ID

Best regards, Dmitry

v-piga-msft · ‎02-25-2018

Hi @dkushner,

From the description of your replay, you should create a calculated table first in the formula below to get the max value of SummAPlus for the each of CounterNumber by DataDateT.

Table =
SUMMARIZE (
    PowerCountersPreviousDay,
    PowerCountersPreviousDay[DataDateT],
    PowerCountersPreviousDay[CounterNumber],
    "Sumsingle", MAX ( 'PowerCountersPreviousDay'[SummAPlus] )
)

Then, you could the measure :

Bussiness Unit =
VAR count1sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 1 )
    )
VAR count139sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 139 )
    )
VAR count149sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 149 )
    )
RETURN
    count139sum + count1sum
        - count149sum

This picture of the result is below:

For more details, you could refer to this pbix file.

Hope it can help you!

Best Regards,

Cherry

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

Additional filter into measure

Helpful resources

NEW! Community Notebooks Gallery

How to Get Your Question Answered Quickly