Forum Discussion

Emranit's avatar
Emranit
Helper II
1 year ago

Need one value from same multiple value for every machine under every date

A DAX need where i will select one date from queue time and select one Machine no from machine. Then MC loading capacity show same multiple value. Here I need one value instead of same multiple value.

 

It needed for every machine under every date.

 

Please see the video.

 

Need Help

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    1 year ago

    Hi Emranit,

    You can try something like this:

    MC loading capacity once Daily = 
VAR __T =
    FILTER( 
        ALLSELECTED('YourTable'),
        'YourTable'[Machine] IN VALUES( 'YourTable'[Machine] ) &&
        /* Make sure 'YourTable' has a Date column with date values */
        'YourTable'[Date] IN VALUES( 'YourTable'[Date] )
    )
VAR __First = MINX( __T, 'YourTable'[Queue Time] )
RETURN

IF( 
    MAX( 'YourTable'[Queue Time] ) = __First,
    SUM('YourTable'[MC Loading Capacity] ),
    0
)
  • Emranit's avatar
    Emranit
    Helper II
    1 year ago

    Dear,

    Thanks for your cooperation. But I'm facing still now problem. MC Capacity value showing 0.

    My requirement is:-

    From my Excel file, if I filter one date from QueueTime it is showing no of 03 MC(FB13, KB03, XB05).

    1) FB13 MC showing same 04(278.4375) value, here will count one value out of same 4 value

    2) KB03 MC showing same 03(1577.8125) value, here will count one value out of same 3 value

    3) XB05 MC showing same 04(525.93755) value, here will count one value out of same 4 value

     

    From each Machine(FB13, KB03, XB05)each one value will be sum 2382.19. So, all actual weight sum will be 2126.90 and MC loading capacity sum will be 2382.19 for Queue Time 09/03/2024.

     

    It will be need for every date like 09/04/2024. 09/05/2024

     

    Note: Data source file is SQL. Here I attached excel file & BI file for more understanding.

     

    I will be more happy for your nice cooperation.

     

    https://drive.google.com/drive/folders/1sotnhB1q-7muY9Yq6MAzLQzNtzEBF-D8?usp=sharing 

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      1 year ago

      Hi Emranit,

      There are some issues with your model and selected fields in Slicers. 
      I've replaced your "Date" column, in Power Query with:

      Table.AddColumn( #"Changed Type", "Date", each DateTime.Date([QueueTime]), type date)

      Important! Used this new "Date" column in the Hierarchy slicer on the left of the page.

       

      Created a supporting measure:

      First QueueTime = 
    CALCULATE (
        MIN ( September[QueueTime] ),
        ALL( September ),
        VALUES( September[Machine] ),
        VALUES( September[Date] )
    )

       

      And this Measure:

      MC loading capacity once Daily = 
VAR __T =
    SUMMARIZE( 
        September,
        September[Machine],
        September[Date],
        September[MC Loading Capacity]
    )
RETURN

IF( NOT ISINSCOPE( September[Machine] ),
    SUMX( __T, [MC Loading Capacity] ),
    IF( 
        MIN( September[QueueTime] ) = [First QueueTime],
        SUMX( __T, [MC Loading Capacity] )
    )
)

       

      With this result.

       

      • Emranit's avatar
        Emranit
        Helper II
        1 year ago

        Thanks for nice solution. Is it possible for summation of MC loading capacity each day without Date column? Actullay in my source fie(SQL Database) there is no Date Column. MC loading Capacity summation base will be QueTime.

         

        Then I need all day MC loading capacity summation.

  • Emranit's avatar
    Emranit
    Helper II
    1 year ago

    https://docs.google.com/spreadsheets/d/1H8aF0qeidZr2v0dxrJfaxx-kSbk0Kgcq/edit?usp=sharing&ouid=113914284006601104314&rtpof=true&sd=true 

     

    I have a DAX which calculate the Maximum RunTime in Minutes among the 10 number of RunTime
    from the Excel file.

    Now I need a DAX which visualize that column Bulk_Production and Dyelot No according to maximum
    RunTime. Like:-

    ## If maximum runtime 549 then visualize from same column
    Bulk_Production 597
    Dyelot 250890013

    Can you do it from the attached Excel file ?

     

    Actually I need which Dyelot run maximum time, according to maximum runtime need Bulk_production quantity & Dyelot no. Then I'll able to understand why this dyelot need to run long time and calculate with prduction.