Forum Discussion

nix's avatar
nix
Icon for Helper I rankHelper I
2 years ago
Solved

Total Employee Detail

I have a voyage table where crew sign-on date,sign off date column is present.
One Crew have done different voyage
I have dim date table also
Now what i want is a date slicer for selected date range(which i can do by fixing a slicer with dim date)
as of now i have related dim date (date column to sign-on column from voyage table)
Now what i  want in table visual is 
the selected date range should filter crew in between that given range of date
means (condition-crew can sign on before selected (from date) or can sign on before to date)
crew sign off date should be blank or less then to date and greater then from date
it should give crew list that they were in signon and sign off in between selected from date and to date
I tried this measure also but its not working

Measure 19 = var earliestDate = MIN(Dimdate[Date])
 Var latestDate=Max(Dimdate[Date])
     
       
       
var _result1 =
CALCULATE(
    DISTINCTCOUNT('Crew Voyage Detail'[crew_id]),
   
       ALL( 'Crew Voyage Detail'),
        'Crew Voyage Detail'[SignOn_DATE] <= earliestDate ||'Crew Voyage Detail'[SignOn_DATE] <= latestDate
        && (( 'Crew Voyage Detail'[SignOFF_DATE] > earliestDate && 'Crew Voyage Detail'[SignOFF_DATE] < latestDate)|| ISBLANK('Crew Voyage Detail'[SignOFF_DATE]) )
    )


RETURN _result1


How to do this
Thanks in Advance

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi nix ,

    Please update the formula of measure [Test] as below and check if it can return the expected result...

    Test = 
VAR Erali =
    MIN ( Dimdate[Date] )
VAR Late =
    MAX ( Dimdate[Date] )
VAR _result1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
        FILTER (
            'Crew Voyage Detail',
            'Crew Voyage Detail'[SignOn_DATE] <= Late
                && (
                    'Crew Voyage Detail'[SignOFF_DATE] >= Erali
                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                )
        )
    )
RETURN
    _result1

    Best Regards

5 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi nix without model and other details, it is hard to spot your issue.

    Still, try test measure below and check picture.

     

    Measure 19 test = 
    var earliestDate = MIN(Dimdate[Date])
    Var latestDate=Max(Dimdate[Date])
             
    var _result1 =
    CALCULATE(
        DISTINCTCOUNT('Crew Voyage Detail'[crew_id]),
       
           ALL( 'Crew Voyage Detail'),
            'Crew Voyage Detail'[SignOn_DATE] => earliestDate 
    ||'Crew Voyage Detail'[SignOn_DATE] <= latestDate
            && (( 'Crew Voyage Detail'[SignOFF_DATE] > earliestDate && 
    'Crew Voyage Detail'[SignOFF_DATE] < latestDate)
    || ISBLANK('Crew Voyage Detail'[SignOFF_DATE]) )
        )
     
    RETURN _result1

     

     

    • nix's avatar
      nix
      Icon for Helper I rankHelper I
      2 years ago

      Exactly,

      How can i do with some date filter and that should give these crew details who signed on on or before max of selected date
      any how i have to relate date column with voyage table right,thats why i did that

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    some_bih Thanks for your contribution on this thread.

    Hi nix ,

    You can follow the steps below to get it:

    1. Please remove the relationship between the table 'Dimdate' and 'Crew Voyage Detail' base on the field [Date] and [SignOn_DATE]

    2. Update the formula of measure [Measure 19]

    Measure 19 =
VAR _seldate =
    SELECTEDVALUE ( Dimdate[Date] )
VAR _result1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
        FILTER (
            ALLSELECTED ( 'Crew Voyage Detail' ),
            'Crew Voyage Detail'[SignOn_DATE] <= _seldate
                && (
                    'Crew Voyage Detail'[SignOFF_DATE] >= _seldate
                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                )
        )
    )
RETURN
    _result1

    3. Create a line chart( X-axis: the field [Date] of the table 'Dimdate'; Y-axis: [Measure 19])

    Best Regards

    • nix's avatar
      nix
      Icon for Helper I rankHelper I
      2 years ago

      Hi Anonymous 
      Thank you so much for your try and solution
      As i am taking Dim date as a slicer with (between value)(mean from date and to date range)
      I use this DAX 

      Test =
      VAR Erali = MINX(VALUES(Dimdate[Date]), Dimdate[Date])
      VAR Late = MAXX(VALUES(Dimdate[Date]), Dimdate[Date])
      VAR _result1 =
          CALCULATE (
              DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
              FILTER (
                  ALLSELECTED ( 'Crew Voyage Detail' ),
                 
                  'Crew Voyage Detail'[SignOn_DATE] <= Late
                      && (
                          'Crew Voyage Detail'[SignOFF_DATE] >= Erali
                              || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                      )
              )
          )
      RETURN
          _result1

      And even dint apply relation with voyage table 
      See here sign off date is also less then from date
      Why i am not getting this
      Please Help
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi nix ,

        Please update the formula of measure [Test] as below and check if it can return the expected result...

        Test = 
VAR Erali =
    MIN ( Dimdate[Date] )
VAR Late =
    MAX ( Dimdate[Date] )
VAR _result1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
        FILTER (
            'Crew Voyage Detail',
            'Crew Voyage Detail'[SignOn_DATE] <= Late
                && (
                    'Crew Voyage Detail'[SignOFF_DATE] >= Erali
                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                )
        )
    )
RETURN
    _result1

        Best Regards