Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to populate my table with a date or period even though is no data so my graph runs continuously along its axis. I came across an artcile below but I seemed to get a mixed results, mainly due on my part most likely.
xreference: Show-dates-with-no-data-on-continuous-date-axis
This is a sample graph, you will notice for 2019 Q2 and Q3 are missing, and again for 2020 we are now in Q4 but no axis details. The actrual raw data is processed through powerquery, what you see here is just one of the 3 data sources, this one is easier to see the gaps on the chart. All 3 tabels look at a calendar I created, and each table has an "ApplicationDate" which points to the date field on my calendar. My calendar date range covers early and future dates to capture all data; the 3 main data soures are refreshed periodically.
This is my sample data: I have a measure that just simple counts the value which is then linked to the calenader against the "ApplicationDate" which is how I get the breakdown by Month and Quarter for each data type. I am maybe needing to cover all date ranges in the calendar and any that are missing from my data soure are populated with a '0' in the "DataValue" column for each table.
Sample Data Type2
ApplicationDate | DataType | DataValue |
02/05/2019 | Data2 | 1 |
29/10/2018 | Data2 | 1 |
27/09/2019 | Data2 | 1 |
27/09/2019 | Data2 | 1 |
03/10/2019 | Data2 | 1 |
18/11/2019 | Data2 | 1 |
18/11/2019 | Data2 | 1 |
08/11/2019 | Data2 | 1 |
22/10/2019 | Data2 | 1 |
28/02/2020 | Data2 | 1 |
28/02/2020 | Data2 | 1 |
09/04/2020 | Data2 | 1 |
Or am I over complicating this task?
many thanks
Chris
Solved! Go to Solution.
@ccarpent - Often you can get around this by doing something like:
Measure =
VAR __Calc = <some calculation>
RETURN
IF(ISBLANK(__Calc),0,__Calc)
@ccarpent , +0 to you measure
or try this option
Amitchandak
Thats for this, definatley worth a look at but for now I think gregs answer seems to work a treat plus I like the idea of using DAX.
thanks again.
Chris
@ccarpent - Often you can get around this by doing something like:
Measure =
VAR __Calc = <some calculation>
RETURN
IF(ISBLANK(__Calc),0,__Calc)
Greg
Thats is so simple, is the measure working through each row; row context, to carry-out the 'IF' statement?
see chart with forumla:
My actual formula is -
=VAR _CALC = COUNTA(Data2_nonWT[FieldName])
RETURN
IF(ISBLANK(_CALC), 0, _CALC)
I take it the fact I have the data2 table linked to the Calenader table (1-* relationship) must also playa key role?
One other query, the red dots on the chart. I can not seem to sort the Months right for 'Q2', the rest seem to fall into place, any thoughts on this?
Chris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |