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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aBerg
Helper II
Helper II

Filtering a Filtered Data

Hi.

 

I have an application for Energy measurements using Pbi Desktop and I need to shown four values visuals, as follows:

 

- Max Demand OutTime and Data&Time of Max Demanda OutTime:

Demand Max OutTime = CALCULATE([Demand Max];FILTER(fEnergy; RELATED(dTime[Hour])< TIMEVALUE("19:00") || RELATED(dTime[Hour])> TIMEVALUE("22:00")))
Demand Max OutTime (hh:mm) = CALCULATE(SELECTEDVALUE(fEnergy[Date&Time]); FILTER(fEnergy; RELATED(dTime[Hour])< TIMEVALUE("19:00") || RELATED(dTime[Hour])> TIMEVALUE("22:00")); FILTER(fEnergy; fEnergy[Demand]= MAX(fEnergy[Demand])))

 

- Max Demand InTime and Data&Time of Max Demanda InTime:

Demand Max InTime = CALCULATE([Demand Max];FILTER(fEnergy; RELATED(dTime[Hour])>= TIMEVALUE("19:00") && RELATED(dTime[Hour])<= TIMEVALUE("22:00")))
Demand Max InTime (hh:mm) = CALCULATE(SELECTEDVALUE(fEnergy[Date&Time]); FILTER(fEnergy; RELATED(dTime[Hour])>= TIMEVALUE("19:00") && RELATED(dTime[Hour])<= TIMEVALUE("22:00")); FILTER(fEnergy; fEnergy[Demand] = MAX(fEnergy[Demand])))

The first two values are shown correctly but the second two values are not. Please if someone can help me why the second two measures are not ok. I have put the measures attached.

 

 

I have already tried to use CALCULATETABLE () but without success. I do not know how to filter using command FILTER ( ) the data already filtered by the CALCULATETABLE. I have tried this:

Demand Max InTime(hh:mm). = CALCULATE(SELECTEDVALUE(fEnergy[Date&Time]);FILTER(CALCULATETABLE(fEnergy; FILTER(fEnergy; RELATED(dTime[Hour])>= TIMEVALUE("19:00") && RELATED(dTime[Hour])<= TIMEVALUE("22:00"))); fEnergy[Demand] = MAX(fEnergy[Demand])))

 

Question_Pbi.png

 

tks,

Berg

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

Hi @aBerg

 

I changed your measure  Demand Max InTime(hh:mm) Test as follows:

 

 

Demand Max InTime(hh:mm) Test =
CALCULATE (
    SELECTEDVALUE ( fEnergy[Date&Time] ),
    FILTER (
        fEnergy,
        fEnergy[Demand]
            = CALCULATE (
                MAX ( fEnergy[Demand] ),
                FILTER (
                    fEnergy,
                    RELATED ( dTime[Hour] ) >= TIMEVALUE ( "19:00" )
                        && RELATED ( dTime[Hour] ) <= TIMEVALUE ( ( "22:00" ) )
                )
            )
    )
)
 
 
At the inner most level filter all fEnergy records for the time between 19 to 22 hours.
Then find the maximum of demand during this period from the filtered records.
Then find the fEnergy - Date&Time which has the record with maximum value of Demand.
 
The only caveat in this solution is what if there are two time slots that have the same demand value during 19 to 22 pm.
In such case you can take either the min or max of date&time instead of selectedvalue.
 
If this works for you please accept this as solution and also give KUDOS.
 
Cheers
 
CheenuSIng
 
 
 
Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

Hi @aBerg

 

Please share the pbix in OneDrive or Google Drive and paste the link here.  Also let me know the output expected.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing 

 

Thanks for reply.

 

PBIX link: https://1drv.ms/u/s!Ag06VMKBGvfCgiuHXDCMkvNrnHWa

 

I need to show in the four fields indicated in the image attached the largest (max) value of the column "Demand" and the time that occured within two periods: one "00:00 to 19:00 and 22:00 to 00:00" (blue period) and another one "19:00 to 22:00" (yellow period):

 

1. In the first field I need the highest (max) value of the "demand" column within the period from 00:00 (00:00 am) to 19:00 (07:00 pm) and 22:00 (10:00 pm) to 00:00 (00:00 am) - blue period.

2. In the second field I need to show at what time this highest (max) value of the "demand" column occurred within the blue period (item 1.).

 

3. In the third field I need the highest (max) value of the "demand" column within the period from 19:00 (07:00 pm) to 22:00 (10:00 pm) - yellow period.

4. In the fourth field I need to show at what time the highest (max) "demand" column value occurred within the yellow period (item 3.). The first three fields worked, but the fourth field did not (blank field). Smiley Frustrated

 

 

 

image.png

 

Thanks !

CheenuSing
Community Champion
Community Champion

Hi @aBerg

 

I changed your measure  Demand Max InTime(hh:mm) Test as follows:

 

 

Demand Max InTime(hh:mm) Test =
CALCULATE (
    SELECTEDVALUE ( fEnergy[Date&Time] ),
    FILTER (
        fEnergy,
        fEnergy[Demand]
            = CALCULATE (
                MAX ( fEnergy[Demand] ),
                FILTER (
                    fEnergy,
                    RELATED ( dTime[Hour] ) >= TIMEVALUE ( "19:00" )
                        && RELATED ( dTime[Hour] ) <= TIMEVALUE ( ( "22:00" ) )
                )
            )
    )
)
 
 
At the inner most level filter all fEnergy records for the time between 19 to 22 hours.
Then find the maximum of demand during this period from the filtered records.
Then find the fEnergy - Date&Time which has the record with maximum value of Demand.
 
The only caveat in this solution is what if there are two time slots that have the same demand value during 19 to 22 pm.
In such case you can take either the min or max of date&time instead of selectedvalue.
 
If this works for you please accept this as solution and also give KUDOS.
 
Cheers
 
CheenuSIng
 
 
 
Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing !

 

Thanks for your very helpful help.

I gave you one KUDO, if it's possible I´d give more ... Smiley Happy

 

Greetings.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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