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

 

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's avatar
aake
Frequent Visitor
9 years ago

 

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'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