Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
Does anyone can found a solution that will help?
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.
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.
Hi @aake
Can you please post a sample file to recreate a solution for you.
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
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
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |