Forum Discussion

DataSkills's avatar
DataSkills
Resolver I
2 years ago
Solved

Bizarre behaviour when adding a measure to a table

Hi, this one has got me!

 

I have a standard table, showing Patient.Name, Level and then a calculation of hours based on this level (x3). 

 

 

I created a measure called Total care mins = sum(PatientAttribute[Level])*3

 

My total time in hours calc is simply: 

Total Care Hours = [Total care mins]/60
 
But the format is not great to show time, so I tried this:
Total Care Hours = Convert([Total care mins]/1440, DATETIME)
 
Unfortunatley, if hours is over 24, it doesn't show more than 24 hours. ie 27 hours and 14 minutes would show as 3:14.  Which is clearly no good. 
 
So, I tried this:
Total care hours = 
Var hrs = [Total care mins]/60
Return
int(hrs) & ":" &  format(round(mod(hrs, 1)*60, 0), "00")
 
The moment I do this, the whole table shows a bunch of extra rows of data where it appears as if some kind of union has happened. I change the formula to:
Total care hours = 
Var hrs = [Total care mins]/60
Return
hrs
All is fine again.  I am at a loss to explain why this would be the case.  The ONLY change I can see happening is that the format of the measure automatically changes from General to Text. But this is still confusing to me as to why it would show this. 

 

becomes...

 

 
I've checked my model. I have one to many relationships between all tables. 
 
I've tried this and still get the weird behaviour:
Total care hours = 1
Any pointers would be gratefully accepted.
Need Help
  • HotChilli's avatar
    HotChilli
    2 years ago

    It's weird if you don't know what's happening but not weird if you do.  Powerbi visuals are very sensitive to the measures that are in those visuals. The visuals show combinations of data in the rows for which a measure has a value.  If the measure value is blank, the row is not displayed.<- (this is important)

    When you create a measure, for example, measureA = 1, and put that in a visual, you will see all combinations of data which produce a non-blank value in the measure.  In this case, the measure is always 1 so you get to see all combinations i.e. lots of rows.

    Now that doesn't really get you any closer to solving your original problem but that's why multiple rows are appearing in the visual.

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    It's weird if you don't know what's happening but not weird if you do.  Powerbi visuals are very sensitive to the measures that are in those visuals. The visuals show combinations of data in the rows for which a measure has a value.  If the measure value is blank, the row is not displayed.<- (this is important)

    When you create a measure, for example, measureA = 1, and put that in a visual, you will see all combinations of data which produce a non-blank value in the measure.  In this case, the measure is always 1 so you get to see all combinations i.e. lots of rows.

    Now that doesn't really get you any closer to solving your original problem but that's why multiple rows are appearing in the visual.

    • DataSkills's avatar
      DataSkills
      Resolver I
      2 years ago

      HotChilli YES! You are 100% right! With your help, I modified my formula as follows:

      var hrs = [Total care mins]/60
      Return
      if(ISBLANK(hrs), blank(),
      int(hrs) & ":" &  format(round(mod(hrs, 1)*60, 0), "00"))
       
      FIXED! Legend! Thank you.