Forum Discussion

aake's avatar
aake
Frequent Visitor
9 years ago
Solved

counting rows that are dependent from two different tables

Hi All, I'm new to Power BI and i have problem on my begining with this app. My problem is that i would like to count the number of orders that where during production in the past. I have 4 tables: ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi aake,

     

    After going over the sample data provided above, I would suggest you to merge "IN" table and "OUT" table into a single table(assume it is called "Merged_IN_OUT") first, then you should be able to use the formula below to create the measure "WIP".

     

    WIP =
    CALCULATE (
        COUNTROWS ( Merged_IN_OUT ),
        FILTER (
            Merged_IN_OUT,
            Merged_IN_OUT[Start] < MAX ( workdays[Workdate] )
                && Merged_IN_OUT[Shipment] < MAX ( workdays[Workdate] )
                && Merged_IN_OUT[LastStatus] = "Shipped"
        )
    )

    Here is the sample pbix file for your reference.

     

    In addition, for more details about how to use Merge Queries in Power BI Desktop, you can refer to the following articles:

    POWER BI DESKTOP : MERGE QUERY OPTIONS

    Shape and combine data in Power BI Desktop

     

     

    Regards

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

Hi aake

 

Can you please post a sample file to recreate a solution for you. 

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

    Hi aake,

     

    After going over the sample data provided above, I would suggest you to merge "IN" table and "OUT" table into a single table(assume it is called "Merged_IN_OUT") first, then you should be able to use the formula below to create the measure "WIP".

     

    WIP =
    CALCULATE (
        COUNTROWS ( Merged_IN_OUT ),
        FILTER (
            Merged_IN_OUT,
            Merged_IN_OUT[Start] < MAX ( workdays[Workdate] )
                && Merged_IN_OUT[Shipment] < MAX ( workdays[Workdate] )
                && Merged_IN_OUT[LastStatus] = "Shipped"
        )
    )

    Here is the sample pbix file for your reference.

     

    In addition, for more details about how to use Merge Queries in Power BI Desktop, you can refer to the following articles:

    POWER BI DESKTOP : MERGE QUERY OPTIONS

    Shape and combine data in Power BI Desktop

     

     

    Regards

    • aake's avatar
      aake
      Frequent Visitor
      9 years ago

      Hi v-ljerr-msft

      Thx for this solution,

      Calculation is not ok, but you showed me how it schould look like.

      What i was trying to get this:

      WIP =
    CALCULATE (
        COUNTROWS ( Merged_IN_OUT ),
        FILTER (
            Merged_IN_OUT,
            Merged_IN_OUT[Start] < MAX ( workdays[Workdate] )
        )
    )  -     CALCULATE (
        COUNTROWS ( Merged_IN_OUT ),
        FILTER (
            Merged_IN_OUT,
                Merged_IN_OUT[Shipment] < MAX ( workdays[Workdate] )
                && Merged_IN_OUT[LastStatus] = "Shipped"
        )
    )

      so it works pretty well :)