Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi,
I built the following matrix
To have the equipments MON01-MO3 and MON01-TORCHERE displayed I checked the "Show value with no data" in the "Rows" section for fields.
My problem is I can't manage to display anything in the columns Total Hours Down, Month Hours and Availability for these rows with no data.
NbHeuresMois =
Hi @Anonymous ,
Not really sure if I understand what is the result you need, you want to show or to hide the rows that have no values?
Appears to me that if you don't have values for the MON01-MO3 and MON01-TORCHERE the values will be presented has they are so with no values in the rows.
Just looking at your Total Hours Down that you refer is from a column on your model there is no data.
Can you explain a little bit better what is the final result what were you expecting to see in the visualization? Values or blanks?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you for helping me.
My apologies for the bad quality of my explanations, this is the context :
The matrix displays for every equipment of an electricity production plant the down time events, and sometime there are no downtimes for one or several equipments in the month (like MON01-MO3 and MON01-TORCHERE in the screenshot example). So there is no values for these 2 equipments and this is normal.
The result I expect is to show in the subtotals for these 2 equipments with no data the count of hours in the month (744) in the month hours column, and a value of 100% in the availability colum.
The values I want to show are in red in the screenshot below :
Thank you again and sorry for my bad english abilities 😅
Regards,
Cado
HI @Cado_one ,
Believe that the issue is related witht he NOT(ISBLANK) part of the measure:
NbHeuresMois =
IF (
AND (
ISINSCOPE ( gmao_BI_Equipement[ID_BI_EQUIP] ),
NOT ( ISBLANK ( MAX ( gmao_BI_Equipement[ID_BI_EQUIP] ) ) )
),
"",
DAY ( EOMONTH ( MIN ( gmao_BI_Equipement[DebutArretEquip] ), 0 ) ) * 24
* DISTINCTCOUNT ( gmao_BI_Equipement[EquipementBI] )
)
What you are doing here is if the value for the ID is blank and is in scope then return the blank try to take out the NOT ISBLANK so your measure should look like this:
NbHeuresMois =
IF (
ISINSCOPE ( gmao_BI_Equipement[ID_BI_EQUIP] ),
,
"",
DAY ( EOMONTH ( MIN ( gmao_BI_Equipement[DebutArretEquip] ), 0 ) ) * 24
* DISTINCTCOUNT ( gmao_BI_Equipement[EquipementBI] )
)
If this does not work please tell, you need to change the inscope because you have values without ID so it does not returns values.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
You are right about the NOT(ISBLANK...) it is not necessary.
After some investigation I realized that the MonthHours measure doesn't calculate (the availability neither subsequently) because the date used in the EOMONTH function is blank for equipments that have no down times !
So I think my problem must me unsolvable in the current case, unless you have an other idea to get the number of days in the month without using EOFMONTH ?
Regards,
Cado
Hi @Cado_one ,
Your issue is with two parts:
Add the calendar table and try to redo your measure to:
NbHeuresMois =
IF (
ISINSCOPE ( gmao_BI_Equipement[ID_BI_EQUIP] ),
"",
DAY ( EOMONTH ( MIN ( gmao_BI_Equipement[DebutArretEquip] ), 0 ) ) * 24
* IF (
DISTINCTCOUNT ( gmao_BI_Equipement[EquipementBI] ) = 0,
1,
DISTINCTCOUNT ( gmao_BI_Equipement[EquipementBI] )
)
)
The use of the auto date time in Power BI always brings issues for the datetime calculation always a good practice to use a calendar table when you have dates.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I created a calendar table and made a relationship between the gmao_BI_Equipement[DebutArretEquip] and the date field of the calendar table.
But when replacing the date hierarchy in the matrix, every downtime events are stucked in the blank year of the hierarchy. Why isn't it recognizing the year and month ?
Sorry this may be a beginner request it's the first time I use a date table. I thought this was not so well adapted to my dataset because there are many different date fields in each table.
Regards,
Cado
Hi @Cado_one ,
The use of a data table is a good practice especially if you have more than one date field since the comparision on different dates can be done at a single time.
You also need to change on your measure the date column tothe date table column.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
It was a hard transformation of my whole dataset but the calendar tables seems to work fine now. And the availabilities also.
I had to create multiple calendar tables because it was not possible to build multiple active relations ships for the tables which contains more than one date field.
It's a little bit ugly fut it does the work !
Thank you and have a nice day !
Cado
Hi @Cado_one ,
There is no need to create multiple calendar tables, you just need to make use of inactive relationships and make measures using the USERELATIONSHIP syntax.
Don't know what is the final result you want bu again the need for multipliying tables should be avoided.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.