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...
Need Help
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.