Forum Discussion

gauravsukhadia's avatar
gauravsukhadia
Frequent Visitor
4 years ago

Trip Distinction between product groups

Hi Team, I have one question, I have huge dataset which contains thousands rows product level details ( fact table ) and each row has Run Sheet ID ( trip on which that line was shipped ) now I want to particularly want to know certain product let's say Product ABC - how many times it went on trip solo and how many times it went on trip with other products. How do i find out ?

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    gauravsukhadia , Try measures like

     

    Product's Single trip


    Countx(addcolumns(summarize(Table, Table[trip ID]), "_1",distinctCOUNT(Table[Product ID]), "_2",calculate(distinctCOUNT(Table[Product ID]), all(Table))),If([_1] =[2], [Trip ID], blank()))

     

    Trip with other

     


    Countx(addcolumns(summarize(Table, Table[trip ID]), "_1",distinctCOUNT(Table[Product ID]), "_2",calculate(distinctCOUNT(Table[Product ID]), all(Table))),If([_1] <[2], [Trip ID], blank()))

  • gauravsukhadia's avatar
    gauravsukhadia
    Frequent Visitor
    4 years ago

    Hi Thank you for your suggestion and it seems extremely complicated but I am trying to replicate your measure. Can you please tell me what does "_1" & "_2" stands for in your measure ??  I tried to just write "_1" & ""_2" and it's not working. I am not sure if it matters or not but I am trying to capture whole product group ( known as "Profile") such as "Mesh" whether it was loaded alone on the truck or loaded with any other group of product. 

     

    Logic is if Mesh product profile was on one run sheet ID alone then it was alone on the truck but if the same Run sheet ID is sharing any other product profile then it will be considered as shared load. One run sheet ID = one truck = One trip.   

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi gauravsukhadia ,

      Could you please provide some sample data in the table Transactions_2020-21 and your expected result with calculation logic and sepcial examples? Thank you.

      For example:

      Sample data:

      Transactions_2020-21

      Product Profile Run Sheet ID ...
      ABC XX XX XX
      ABC XX XX XX

      Expected Result:

      Product times of on trip solo  times of on trip with other products
      ABC 1 3
      BCD 2 3

      Best Regards