Forum Discussion
Solved
Start and end time
Hi there, pretty new to Power BI. I'm trying to replicate something I do in Excel in Power BI, mainly because excel can't handle the amount of data that's being processed. I'm trying to figure o...
Hi jojo54
See if the following can fix it for what you need
Number of clients 2 = VAR vMinVal = MIN ( TimeTable[Value] ) VAR vMaxVal = MAX ( TimeTable[Value] ) VAR vNumberOfClients = COUNTROWS ( FILTER ( ALLSELECTED ( ClientsTbl ), ClientsTbl[Entry Time] <= vMinVal && ClientsTbl[Exit Time] >= vMaxVal ) ) RETURN vNumberOfClientsWhenever I have more time I could think of something better.
Geradav
Responsive Resident
Responsive ResidentHi jojo54
Here is an alternative using DAX
Number of clients =
VAR vMinVal =
MIN ( TimeTable[Value] )
VAR vMaxVal =
MAX ( TimeTable[Value] )
VAR vClientEntry =
CALCULATE (
COUNTROWS ( ClientsTbl ),
TimeTable[Value] <= vMinVal,
ALL ( TimeTable )
)
VAR vClientExit =
CALCULATE (
COUNTROWS ( ClientsTbl ),
TimeTable[Value] >= vMaxVal,
ALL ( TimeTable ),
USERELATIONSHIP ( ClientsTbl[Exit Time], TimeTable[Value] )
)
RETURN
MIN ( vClientExit, vClientEntry )Create a time table, then create a relationship between Start Time and the time value, and a second one between Exit Time and the time value. One will be an active relationship, while the second will be inactive.
Like this:
Then you can use that measure in your visualization. Time value in X axis and the measure in the Value area
And you can also make use of the same DAX code in the data model of Excel (Power Pivot)
Let us know what works for you
