Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need help setting up a logistics visual

I need to make a dashboard that shows what items that sells good in different warehouses. so if warehouse L10 sells a certain item as item class code vk2 (the bad class) then i whant it to show if ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Anonymous

     

    Hi Robin,

     

    That's OK. I found a solution. Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRhSiy4U2C9zaXVG

    1. Create a new table "WH".

    WH =
VALUES ( 'Table1'[WareHouse Number] )

    2. Establish relationship.

    3. Create a measure.

    FinalMeasure =
VAR selectedWH =
    IF (
        HASONEVALUE ( WH[WareHouse Number] ),
        VALUES ( WH[WareHouse Number] ),
        BLANK ()
    )
VAR selectedWHvk2 =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Item Class] = 2
                && 'Table1'[WareHouse Number] = selectedWH
        )
    )
RETURN
    CALCULATE (
        IF (
            MIN ( 'Table1'[Item] ) IN selectedWHvk2
                && MIN ( 'Table1'[Item Class] ) = 1,
            1,
            0
        ),
        ALL ( WH )
    )

    4. Create several visuals.

    5. Filter the result. (Visual level filter)

     

     

     

     

     

     

     

     

     

     

     

     

     

    Note: 1. The slicers are from different tables. Don't mix up.

    2. All the columns are needed in the visual as the picture showed. 

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Anonymous,

 

Hi Robin,

 

You can try this measure.

Measure =
VAR allSoldBad =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Item Class] = "vk2" )
    )
RETURN
    IF (
        MIN ( 'Table1'[Item] ) IN allSoldBad
            && MIN ( 'Table1'[Item Class] ) = "vk1",
        "GoodInAnother",
        "NoChanges"
    )

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Yes it dose work, but is it possible to make it show what warehouse number its good at?

The example i made is just a few rows. but the actual data is 10 of thousands of item numbers

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    In the sample, we can use this measure to list the warehouses in one row. One precondition: one item only has one class in a special warehouse.

     

    Measure 2 =
VAR allSoldBad =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Item Class] = "vk2" )
    )
RETURN
    CONCATENATEX (
        FILTER (
            SUMMARIZE (
                'Table1',
                'Table1'[WareHouse Number],
                'Table1'[Item],
                "IfGood", IF (
                    MIN ( 'Table1'[Item] ) IN allSoldBad
                        && MIN ( 'Table1'[Item Class] ) = "vk1",
                    1,
                    0
                )
            ),
            [IfGood] = 1
        ),
        [WareHouse Number],
        "-"
    )

     

     

     

     

     

     

     

     

    Or use the visual level filters.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Dale

      I think we are missunderstanding each other, this dosent seam to work for me =/
      I have made a pivot so far, and got a very clunky list over item numbers with item classification.

      Here we can see all the warehouse number sorted by item classification 1 - 2
      and we can see what item is in item class 1 in 1 warehouse and item class 2 in the other.
      So my question to you is,
      Can we filter this somehow? so we only see the item numbers that are actualy in stock at 2 different warehouses with 2 different classifications?

      I am very sorry if im explanin this poorly, english is not my first language
      - Robin

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Anonymous,

         

        Hi Robin,

         

        We can add a calculated column to the table. Then we can use this column in the slicer.

        Status =
VAR statuss =
    CALCULATE (
        DISTINCTCOUNT ( [Item Class] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Item] ),
            'Table1'[Item Class] IN { 1, 2 }
        )
    )
RETURN
    IF ( statuss = 2, 1, 0 )

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        You can check the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgRhSiy4U2C9zaXVG

         

        Best Regards!

        Dale