Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Not able to select day, hour function inside a measure

Hi,

 

I have a measure named Timelevel, based on its values, i need to dispaly month,day or hours accordingly. I am not able to achieve the days and hours, the column is production date time, in order to use this column inside a measure, i used firstnonblank and am getting the first nonblank value i.e value 1 for day but i need to the other day values as well, i tried using values, selected value but its throwing error.

 

When [Time Level] is “M” these graphs should show months formatted like Aug 1 (only 1 value for each month)
when it is “D” these graphs should show Days,like Aug 1, Aug 7(many values of days for a month)
when it is “H” these graphs should show hours, and when it is “E” they show the exact date-times.

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, i create a sample to test:

    Please try to create a slicer table first:

    Then try to create this column:

    Year&Month = 'Table'[Production Date-Time].[Year]&"-"&'Table'[Production Date-Time].[MonthNo]

    Then try this measure:

    Measure = 
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[Level] ),
    "D", FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD" ),
    "E", FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD HH:MM:SS" ),
    "H", HOUR ( MAX ( 'Table'[Production Date-Time] ) ),
    "M", MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Year&Month] IN FILTERS ( 'Table'[Year&Month] )
        ),
        FORMAT ( 'Table'[Production Date-Time], "MMM DD" )
    )
)

    When you select one value in slicer, it shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This is the code that i am using. xaxis date choice is the measure that i created, when timelevel value is "M", it should display only month values, when time level is D, days should be shown, since i am using a measure, unless i use some function like values,firstnonblank i am not able to slelect the production date time value inside a measure, when i use values or selected values in place of firstnonblank, am not getting the output. 

       

      xaxis date choice = if([Time level]="M",FIRSTNONBLANK('Fact'[Production Date-Time].[Month],[Time level]="M"),
      if([Time level]= "D", FIRSTNONBLANK('Fact'[Production Date-Time].[Day],[Time level]="D")))
       
      Time level = if([test date diff]>=90,"M",(
      if([test date diff]>=4,"D",(
      if ([test date diff]>=1,"H","E")))))
  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    According to your description, i create a sample to test:

    Please try to create a slicer table first:

    Then try to create this column:

    Year&Month = 'Table'[Production Date-Time].[Year]&"-"&'Table'[Production Date-Time].[MonthNo]

    Then try this measure:

    Measure = 
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[Level] ),
    "D", FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD" ),
    "E", FORMAT ( MAX ( 'Table'[Production Date-Time] ), "YYYY-MM-DD HH:MM:SS" ),
    "H", HOUR ( MAX ( 'Table'[Production Date-Time] ) ),
    "M", MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Year&Month] IN FILTERS ( 'Table'[Year&Month] )
        ),
        FORMAT ( 'Table'[Production Date-Time], "MMM DD" )
    )
)

    When you select one value in slicer, it shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks a lot, this works. there are some minor changes required, I don't need the level slicer, the time level should be calculated based on the date slicer selection (between date slicer) . I was getting error when i was using switch, i achieved it using nested if, can you please convert this into switch, i am getting conversion error and its asking to use format or value function.

       

      measure test1 = if([Time level]="D",FORMAT ( MAX ( 'Fact'[Production Date-Time] ), "YYYY-MM-DD" ),
      if([Time level]="E", FORMAT ( MAX ( 'Fact'[Production Date-Time] ), "YYYY-MM-DD HH:MM:SS" ),
      if([Time level]= "H", HOUR ( MAX ( 'Fact'[Production Date-Time] ) ),
      if([Time level]="M", MINX (
      FILTER (
      ALLSELECTED ( 'Fact' ),
      'Fact'[Year&Month] IN FILTERS ( 'Fact'[Year&Month] )
      ),
      FORMAT ( 'Fact'[Production Date-Time], "MMM DD" )
      )
      ))))