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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Card with states,using both time and date for calculation

I am working on card with states,I have a measure that is late = TIME(09,05,00) and this is used as a state.
What happened recently is that the late criteria is changed from 9:05 to  9:35, I should be able to see the state changing not only with the late measure but also corresponding dates should be considered for calculating the late.

 

Date                Time    Late  Changes

11/30/2019     9:07     Yes    Before

12/01/2019     9:36     Yes    After

12/03/2019     9:01     No     After

 

So when I click on 12/03,my card should not display late color,since this is updated now.
How can I achieve this in DAX?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

t tweeked a bot and created a calculated column it works well,but if I create a measure and use it in my card with states visual,I dont get to see that option where you can enter the condition to be matched for returning appropriate action in the card visual.

This is my measure

late_12 =
VAR dat =
value( emp_data[Date - Copy] )
VAR ti =
value( emp_data[first_in] )
VAR datein =
DATE ( 2019, 04, 15 )
VAR be =
TIME ( 09, 05, 00 )
VAR aft =
TIME ( 09, 35, 00)
RETURN
IF (
dat < datein
&& ti > be,
1,
IF ( dat >= datein && ti > aft, 1, 0 )
)
this is my calcualted column,
late_12 =
VAR dat =
max ( emp_data[Date - Copy] )
VAR ti =
max ( emp_data[first_in] )
VAR datein =
DATE ( 2019, 04, 15 )
VAR be =
TIME ( 09, 05, 00 )
VAR aft =
TIME ( 09, 35, 00)
RETURN
IF (
dat < datein
&& ti > be,
1,
IF ( dat >= datein && ti > aft, 1, 0 )
)
I also dont get to see the median time value on a population level showing different state level ,when the time is say 9:35,I should be seeing a red color text,where as I get to see the black color text,this is default.
How to achieve this after having done all the other things,how to achieve the state at population level.

View solution in original post

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can create a measure as below to work on it.

late = 
VAR dat =
    MAX ( 'Table'[date] )
VAR ti =
    MAX ( 'Table'[Time] )
VAR datein =
    DATE ( 2019, 12, 01 )
VAR be =
    TIME ( 09, 05, 0 )
VAR aft =
    TIME ( 09, 35, 0 )
RETURN
    IF (
        dat < datein
            && ti > be,
        "yes",
        IF ( dat >= datein && ti > aft, "yes", "no" )
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @Anonymous ,

 

We can add the measure to the card and it will be dynamic by the selected value in the slicer.

late = 
VAR dat =
    MAX ( 'Table'[date] )
VAR ti =
    MAX ( 'Table'[Time] )
VAR datein =
    DATE ( 2019, 12, 01 )
VAR be =
    TIME ( 09, 05, 0 )
VAR aft =
    TIME ( 09, 35, 0 )
RETURN
    IF (
        dat < datein
            && ti > be,
        "yes",
        IF ( dat >= datein && ti > aft, "yes", "no" )
    )

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

I don't have a yes or no card,I am showing the actual time like this 09:30:05,so if I click on a filter it works but on a overall level it doesnt change states.

Also with a measure I cant use Max,I had to use the value().I hope you get my concern here. Can you please help on this

Anonymous
Not applicable

t tweeked a bot and created a calculated column it works well,but if I create a measure and use it in my card with states visual,I dont get to see that option where you can enter the condition to be matched for returning appropriate action in the card visual.

This is my measure

late_12 =
VAR dat =
value( emp_data[Date - Copy] )
VAR ti =
value( emp_data[first_in] )
VAR datein =
DATE ( 2019, 04, 15 )
VAR be =
TIME ( 09, 05, 00 )
VAR aft =
TIME ( 09, 35, 00)
RETURN
IF (
dat < datein
&& ti > be,
1,
IF ( dat >= datein && ti > aft, 1, 0 )
)
this is my calcualted column,
late_12 =
VAR dat =
max ( emp_data[Date - Copy] )
VAR ti =
max ( emp_data[first_in] )
VAR datein =
DATE ( 2019, 04, 15 )
VAR be =
TIME ( 09, 05, 00 )
VAR aft =
TIME ( 09, 35, 00)
RETURN
IF (
dat < datein
&& ti > be,
1,
IF ( dat >= datein && ti > aft, 1, 0 )
)
I also dont get to see the median time value on a population level showing different state level ,when the time is say 9:35,I should be seeing a red color text,where as I get to see the black color text,this is default.
How to achieve this after having done all the other things,how to achieve the state at population level.
Greg_Deckler
Super User
Super User

I'm not understanding what you are requesting. Are you saying that for past dates the late status should be calculated at the old time and for the new dates they should be calculated at the new time?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Exactly,till 01-05-2019,the late time was 9:05 AM,after 01-05-2019,the late time is 9:35 AM.I should have a single measure that I could link with the card with states visual.I hope you get my question now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.