Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am trying compare 2 tables with dates to confirm if we have recieved an item
I have tried to minus the 2 related tables as follows:
Status =
SUMX('Reporting Schedule',
(DISTINCTCOUNT('Reporting Schedule'[Submission Period +1]))
-
(DISTINCTCOUNT(_A_Combined[spm_reporting_period]))
)
The expectation (or hope) is i would receive
Solved! Go to Solution.
@Anonymous
Ok, see if this is what you need.
BTW, the relationships between your Date table and the fact tables are many-to-many (which can generate unexpected results and should be avoided).
Since we are looking at periods, I'va added a Period Table as a dim table for both fact tables to be used in the visuals (thus avoiding the dreaded many-to-many relationships from the Date table).
The new measures used:
1) Outstanding:
Outstanding (New) =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(Sched, _Received))
2) Received:
Received (New) =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(INTERSECT(Sched, _Received))
3) Unexpected:
Unexpected New =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(_Received, Sched))
And this is the end result:
See if this is the end result you need.
Attached is the new PBIX file
Proud to be a Super User!
Paul on Linkedin.
Hi @Anonymous ,
Had a look at your data model and it looks like there are lot of things which needs to be corrected. Few of them are:
1) The Received table does not contain the exact date, so what will be indicator if the product has been received?
2) The relationship between the Expected and Received table should be on the basis of contract and not the dates. Currently it is between Reporting period which is of text data type and Date having DateTime datatype which doesn't make sense at all.
So in conclusion, you will have to look for a unique column in both the tables based on which the relationship can be formed to carry out further operations.
And the datatypes of the columns needs to be corrected. Also, look at the required information of receiving date so that you can get the desired result.
hi @PC2790
Thanks for the reponse
I have updated the model slightly, as it wasnt truly reflecting which is available on the file and the links.
to try and articulate what i am trying to acheive.
We have a schedule for each contract, as present the team manually populate an excel sheet with the date the submissions is received
This table is then unpivoted in powerbi to leave 2 columns, removing all those that have no content
Within the dynamics database. the data will contain A line for each metric and Type of contract, contract number and Reporting Period
They are linked to each other via
What i am trying to acheive is as follows
what we are trying to achieve is how much work is left for the team to action. I f there is another way im missing, please let me know, i thought this may be the easiest way as both will have the reporting period in the same format.
Thanks again
As per my understanding that I am able to get with the provided information, I would suggest to create a unique column which will be used to for a common attribute in the Received and Reporting Table.
I created a new column in both the tables as:
Thank you for the responses. I have updated the dax slightly, as i released the reporting schdule would only have 1 value and the recieved file will contain mutiple and require distinctcount i beleive (a line for each metric), however still experieing some issues.
Status =
SUMX('Reporting Schedule',
(COUNT('Reporting Schedule'[Expected date]))-
(DISTINCTCOUNT('Received'[Reporting Period]))
)
Links to the files below, the relationships im fear i am stuck with as there are a lot more tables linked in to this file as it pulls data from 3 main source (Dynamics for the performance data and 2 master tables which are excel based at present for finance)
Actual file relationship
Attached file
@Anonymous
I'm not too sure what exactly you are trying to identify. I have built the following under the assumption that you are comparing Contracts, type and periods between the two tables:
1) To calculate Contracts Scheduled but not received:
Scheduled not received =
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(Sched, _Received))
2) To calculate contracts scheduled & received:
Scheduled & received =
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(INTERSECT(Sched, _Received))
3) To calculate contracts received but not scheduled:
Received not Scheduled =
VAR Sched = SELECTCOLUMNS('Reporting Schedule', "Contract", 'Reporting Schedule'[Contract Number], "Type", 'Reporting Schedule'[Type], "Period", 'Reporting Schedule'[Reporting Period])
VAR _Received = SELECTCOLUMNS('Received', "Contract", 'Received'[Contract Number], "Type", 'Received'[Type], "Period", 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(_Received, Sched))
Which gets you this:
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown
I think I may have over complicated in the first request
We have a submission tracker when submissions are to due to be received, the column header is the reporting period
We have database of what is received.
These are linked to each other by the contract number (work task) and a date table.
We are trying to confirm
What is in the submission tracker and present
The URN between each table is the Contract number / Work task
We are only interested if we have received a submission, not the volume of submissions received in any given month that match the URN.
Using the example:
The schedule tables confirm when we would be expecting a delivery / submission. The received table (the database) confirms when the delivery has been received and the number of items (Metrics).
Is it possible using measures to
I have updated the source files to hopefully paint a better picture
@Anonymous
Ok, see if this is what you need.
BTW, the relationships between your Date table and the fact tables are many-to-many (which can generate unexpected results and should be avoided).
Since we are looking at periods, I'va added a Period Table as a dim table for both fact tables to be used in the visuals (thus avoiding the dreaded many-to-many relationships from the Date table).
The new measures used:
1) Outstanding:
Outstanding (New) =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(Sched, _Received))
2) Received:
Received (New) =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(INTERSECT(Sched, _Received))
3) Unexpected:
Unexpected New =
VAR Sched = SUMMARIZE('Reporting Schedule', 'Reporting Schedule'[Contract Number], 'Reporting Schedule'[Reporting Period])
VAR _Received = SUMMARIZE('Received', 'Received'[Contract Number], 'Received'[Reporting Period])
RETURN
COUNTROWS(EXCEPT(_Received, Sched))
And this is the end result:
See if this is the end result you need.
Attached is the new PBIX file
Proud to be a Super User!
Paul on Linkedin.
This is great, i start to understand it a bit more now. it is a steep learning curve which i learning more by each day.
Thank you, i really appreciate the help.
Lee
Hi @Anonymous ,
The logic of DAX expression that you have applied looks good and should solve the purpose, however it will fail in various conditions like: null or blank values in any of the columns
Please provide the sample data for trying the implementation. Also, please explain clearly what issue you are facing here.
Hi @Anonymous
Can you show a sample of your 2 tables?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |