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

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 :)