counting rows that are dependent from two different tables

[aake]

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:

1 Table containing only working days dates since begining of the year

2 Table With production orders containing Order number and Item number

3 Table Of start dates for each order with Order number and Start Date

4 Table Of End dates for each order with Order number and End Date.

What i would like to do is to present this data on the chart where on X axis are days from first table (working Days) and on Y axis would be value of order that was during production on each day (value) on X axis. It would be simply measured as count of Order Numbers (from second table), where Start Date (table nr. 3) < Workdate ( X value)  minus count of Order Numbers (from second table), where End Date (table nr. 4) < Workdate ( X value).

Previusly i was using Qlik app for this and it was very simple. Here i can not do this and i'm starting to worry that this is not possible.

I'm also using this data to calculate number of order opened and closed each Workday (X value) and i don't have problem with this.  I think that in Excel it would be simply "countifs" function but i was trying to use many of solutions that i fount on this forum and i had some results but the problem was that when i tried to add filter by item number ( to check number of orders during production for specific item number) then it does not apply for calculated value. Anyone got some idea witch function i should use in Power BI? 

[aake]

Does anyone can found a solution that will help?

[BhaveshPatel]

Hi @aake

The Queen of all the functions in DAX -- You should use CALCULATE function to pass on a filter to your count records.

See this VIDEO from Rob. 

[aake]

Hi Bhavesh,

I tried to use calculate function as new column in first table ( DGS_Calendar it is called - the one with workdays) ind i always have the same problem:

 which i don't understand because Countrows parameter is table, and in each filter there is single column.

[BhaveshPatel]

Hi @aake

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

[aake]

Hi @BhaveshPatel

File samples are in location below

 https://1drv.ms/f/s!AgBIHH6nH3zFgtsuaWdtkA5kusSKzw

the idea is to create combo chart where on X is workday and on Y there are values of intake, output and work in progres for each day using data from location

[v-ljerr-msft · Accepted solution]

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]

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 🙂