Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Display measure in matrix with no data rows

Hi,

 

I built the following matrix 

Cado_0-1618303445888.png

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.

 

  • The Total Hours Down column is calculated in the Query Editor, it's difference between two datetime columns.
  • The Month hours column is a DAX 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])
)
  • The Availability column is also a DAX measure :
Dispo = 
IF(
AND(ISINSCOPE(gmao_BI_Equipement[ID_BI_EQUIP]),NOT(ISBLANK(max(gmao_BI_Equipement[ID_BI_EQUIP])))),
"",
([NbHeuresMois]-SUM(gmao_BI_Equipement[StopTime]))/[NbHeuresMois]
)
 
What conditions should I use in my measure for it to be visible in the matrix for no data rows ?
 
Thanks in advance,
Regards,
Cado
9 REPLIES 9
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @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 :

Capture d’écran 2021-04-14 163147.png

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @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:

  • Lack of use of a calendar table so that you have all the dates filled even if there are no values
  • The use of the distinct since when there are no values it pick the values

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.