Forum Discussion
Excellove15
Dec 11, 2024Iron Contributor
Dax measure not considering the filter context and not aggregating
Hi, I have the below visual and highlighted Dax is having problem: Below is Dax: noofdays = COUNTROWS(Calendar_) For the below selected filters, noofdays dax should display 60 for month ...
SergeiBaklan
Dec 12, 2024MVP
You need to keep filter on Points
SUMX(
VALUES( Calendar_[Month-Year] ),
SUMX(
VALUES( Points[DBName-Point_Id] ),
VAR _target = CALCULATE(
AVERAGE( TargetTimeSeriesMonth[Dail Units] ),
TargetTimeSeriesMonth[TargetType] = 1
)
VAR _monthtarget = CALCULATE(
SUM( TargetTimeSeriesMonth[Usage] ),
TargetTimeSeriesMonth[TargetType] = 1
)
VAR _noofdays = CALCULATE(
COUNTROWS( Calendar_ ) * COUNTROWS( Points ),
KEEPFILTERS( Points )
)
VAR _nofinvdays = CALCULATE(
COUNTROWS( DataInvoice ),
KEEPFILTERS( Points )
)
VAR _missing = _noofdays - _nofinvdays
VAR Result =
IF(
_missing = _noofdays,
_monthtarget,
_target * _missing
)
RETURN Result
)
)
- Excellove15Dec 12, 2024Iron Contributor
Hi Sir,
Thanks for your quick response and a wonderful solution! 🙂
The totals match perfectly!
When I tested your Dax measure, I found the below in the visual:
I took the variables from Forecast Budget Unit (Timeseries) Dax and created 3 new separate measures to test it.
Found that missing Dax is in negative (-62) and noofdays Dax contains 31 days. This makes me doubt that I made some mistake in Dax of noofdays. I am struggling to figure it out.
My requirement for:noofdays Dax - how many days are there in the given month-year for each point i.e at a point level
nofinvdays Dax- how many invoice days are there for the given month year and point
missing Dax - therefore how many days do i have missing
For example, April 2024 has 30 days, and a particular point might have 20 days in the invoice. so 30-20=10 missing days for that point in that month
therefore 10*average forecast rate (_target variable in above measure) = your forecast budget Unit (Timeseries).
Above 3 days Dax form integral part of Forecast Budget Unit (Timeseries). Could you please help me with logic?PFA file here Portfolio Performance - v2.15 (1).pbix
Thanks in advance!
- SergeiBaklanDec 14, 2024MVP
I guess you have to exclude credits, i.e.
nofinvdays= CALCULATE( COUNTROWS( DataInvoice ), DataInvoice[Invoice / Credit] = "1" ) - CALCULATE( COUNTROWS( DataInvoice ), DataInvoice[Invoice / Credit] = "-1" )
But that's only guess, I have no idea how this business works.
- sivarajanDec 20, 2024Copper Contributor
Hi Sir,
Thanks for your quick response!
Apologise for the delay in getting back as there was back to back testing and calls
Your dax measure is pretty close and it gave us an idea where we went wrong.
In below dax we missed distinct count of Invoice date which was giving both credit and debit:Hats off to your knowledge sir! though you are not aware of our business but still you managed to be close. This is amazing talent sir🙂
I will accept your solution and close this query
Thanks a lot