Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm trying to create a Line Chart to show the Average and also the UCL and LCL.
To calculate this, I have the following metric:
[No. of Incidents]
[Incident_Month_Year]
I also have some additional columns as well, but they aren't relevant.
I calculate the No. of Observed Months:
Observations Tot Incidents = CALCULATE( DISTINCTCOUNT('Table1'[Incident_Month_Year]),ALLEXCEPT('Table1',[Incident_Month_Year]))
Which I use to to draw out the Average No. of Incidents Per Month
Avg Cnt Incidents = CALCULATE(sum('Table1'[Tot_#_Incidents])/[Observations Tot Incidents], ALLEXCEPT('Table1','Table1'[Incident_Month_Year]))
Unfortunately, when I lay out the above onto a line chart, i see that the Avg Cnt Incidents and Observation Tot Incident Measures have extended themselves to dates prior to what I have in [Incident_Month_Year].
Is there any way to filter out my DAX correctly to only show those months which have data?
Solved! Go to Solution.
Hi @Anonymous
insert your DAX calculation (for Observations Tot Incidents) in this IF statement.
[Observations Tot Incidents] =
IF ( ISBLANK ( [Tot_#_Incidents] ), BLANK (), <DAX for Observations Tot Incidents> )
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous ,
at first try to change this...
Avg Cnt Incidents = CALCULATE(DIVIDE(sum('Table1'[Tot_#_Incidents]),[Observations Tot Incidents]), ALLEXCEPT('Table1','Table1'[Incident_Month_Year]))
Hi @mwegener ,
Made the changes, but still no changes in the Line Chart as it still is showing information to Jan 2018. My data only goes back to June 2018!
Hi @Anonymous ,
could you give an overview of your model and the values on your line chart?
I'm trying to create a Line Chart which showcases the average of the Total No. of Incidents across the available months of data as a straight line.
I currently have a column called [Tot_#_Incidents] with Values for each Month up until June 2019.
When I try to calculate the number of observations and average, I can see that it extrapolates to include months which I have no data. However, the number of observed month is correct as 19 is the no. of months from June 2018 - December 2019.
Is there any way I can filter out the Measure used to only include those months where No. of Incidents > 0 ?
Hi @Anonymous ,
i think so. Could you post your measure definition?
Can't you put a visual filter (line chart) on the measure [Tot_#_Incidents]?
Hi @mwegener ,
Yes - aware of the Visual Filter, but would rather make the modications on the Measures.
@Anonymous
You may take a look at the post below and try ISEMPTY if necessary.
https://community.powerbi.com/t5/Desktop/Forecasts-for-year-and-actuals-for-ytd-only/td-p/825702
Hi @Anonymous
insert your DAX calculation (for Observations Tot Incidents) in this IF statement.
[Observations Tot Incidents] =
IF ( ISBLANK ( [Tot_#_Incidents] ), BLANK (), <DAX for Observations Tot Incidents> )
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |