Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power BI Not Showing Calculations for Unmatched Records

I have been trying to combine two reports in a one to many relationship for our HR department.  One report is a list of open positions (FulfillmentStatus one side) and the other is a list of job requisitions (FISReqStatus many side) that have been opened to fill the postions.  I created a bridge table between both reports and have setup a connection across both tables. 

 

We want to be able to show in one view all relevant stats from the two reports even when there are no job reqs yet opened.  Other than job requestor, contact profile, and source; the only statistic coming from the open positions is the quantity requested.  All other stats come from the job requisitions, i.e. total applicatins, count of req ids, etc.  I have set it to show items with no data and I am getting exactly what they want with the exception of quantity requested column that wont show any data for positions that have no matching job reqs. 

 

See below, If I add the Req ID quantity requested disappears for these records, yet the correct total of 233 is still shown.  When I filter to just records with a matching Req ID everything looks fine.  Likewise if I remove the Req ID and filter on only records with no matching Req ID things again look fine, but the moment I add the Req ID the quantity requested dissapears for those unmatched records.  Am I missing something here?

 

Calculation Issue'.jpg

 

Calculation Issue Data Model.JPG

 

1 ACCEPTED SOLUTION
Pragati11
Super User
Super User

Hi @Anonymous ,

 

The reason why the QuantityRequested disappears at the moment you add REQ ID column is -

In your FIRREQSTATUS table, there are only blank values for TechOpsRequestID column. So basically it's empty for this column. See below:

Pragati11_0-1597764428904.png

Therefore you see blanks in your table visual.

 

I think it's worth checking the data and cleaning it.

 

Also a workaround for your issue would be as follows:

I modified the data model as follows:

I created relationship in the 2 tables on "TechOpsRequestID" column.

Pragati11_2-1597764825153.png

 

Create a measure as follows:

QantityRequestedMeasure = SUM(FullfillmentStatus[Quantity])+0 (as suggested by @dedelman_clng )

 

 Then move this measure on your table visual:

vt.png

 

Hope this helps.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

15 REPLIES 15
Pragati11
Super User
Super User

Hi @Anonymous ,

 

The reason why the QuantityRequested disappears at the moment you add REQ ID column is -

In your FIRREQSTATUS table, there are only blank values for TechOpsRequestID column. So basically it's empty for this column. See below:

Pragati11_0-1597764428904.png

Therefore you see blanks in your table visual.

 

I think it's worth checking the data and cleaning it.

 

Also a workaround for your issue would be as follows:

I modified the data model as follows:

I created relationship in the 2 tables on "TechOpsRequestID" column.

Pragati11_2-1597764825153.png

 

Create a measure as follows:

QantityRequestedMeasure = SUM(FullfillmentStatus[Quantity])+0 (as suggested by @dedelman_clng )

 

 Then move this measure on your table visual:

vt.png

 

Hope this helps.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

I just created a new column that looksups the TechOpsRequestID and if it finds nothing I told it to return "No Match" instead of blank and removed all the blanks from the Fulfillment table and bridge tables, but sadly no luck.

@Anonymous can you go into a little more detail on your data model?  Usually a 1-to-many relationship can have "1"s with 0 "many"s, but rarely does it have "many"s with 0 "1"s.

 

The model in your file has TechOpsRequestID (1) to CleanJustification (many).  Are there CleanJustification that do not have a match in TechOpsRequestID ? If so you may need to re-think the model that you want.

 

In either case you should not have to be building the model by adding columns once it is all loaded into PowerBI.  Some data cleansing can be done in Power Query (Transform Data), but there may be issues with the data beyond which M or DAX can handle (easily).

 

Also, in general if the 1-to-many relationship is correct, you should not need so many calculated columns - measures should do the exact same thing (without all of the "X" iterators as well), and then they can become "columns" in a table visual.

 

I'd suggest taking a step back and looking at the data and if it is really modelled the way you want.  I'll probably get roundly booed for this (😁), but you may possibly need to mock it up in Excel with Vlookups and pivot tables.

 

Thanks

David

Anonymous
Not applicable

So the data sets I have been given are 2 excel spreadsheets, one is an extract from a server showing all job reqs ever opened, ReqStatus table.  The other is a spreadsheet someone in HR has manually been tracking all open Postitions for our part of the organization, FulfillmentStatus table.  This person has been manually going back and forth between the two to try and figure out which had reqs opened, candidate status etc.

 

So the ReqStatus table absolutely has a huge number of reqs not tied to the current positions list as it is a history of all and contains ones not associated with the positions in our part of the org.  As for the clean justification field I had to extract that from the business justification field which was a freeform text field that sometimes contained the TechOpsRequestID as part of the string.  Going forward users will only provide the TechOpsRequestID so I won't have to keep doing that and Business Justification will become the key. 

 

That said, just because I have tried everything, I also added a column to ReqStatus in Excel that indexes i nteh correct TechOpsRequestID and if it finds no ID it returns "No Match".  I then used that field as the key between the two tablesd, but still no luck.  My next solution will be to remove any ReqStatus records that are not part of our open position list, but that seems like it shouldn;t be necassary.

Anonymous
Not applicable

As if this could behave anymore strangely, look at this.  If I move ReqID in front of the other columns, their data disappears too.  Then if I move ReqID all the way to the start, it reduces my view to only records with a matching ReqID even though I have Show Items with no Data checked, yet it still shows totals for all records, even unmatched ones...

 

ReqID at EndReqID at End

ReqID in MiddleReqID in MiddleReqID at BegginingReqID at Beggining

dedelman_clng
Community Champion
Community Champion

Hi @Anonymous 

 

Try adding "+0" to the end of your measure that is counting the open reqs. If that doesn't work, please share your measure code and/or a sample PBIX.

 

Hope this helps

David

 

P.S. - A one-to-many relationship shouldn't need a bridge table - those are generally needed for resolving many-to-many.

Anonymous
Not applicable

I actually have not been using measures as they have been causing odd issues with these data sets.  Either they match every Req with every open position or they just never update when they are added to the table grid.  I did create a measure to test your idea, but it again wont update, unless I add it to the table grid then edit the formula, but once I got it to update I still had the same issue.  What I have been doing is creating calculated columns in the positions table to capture all relevant stats from the Req table, which also allows me to show the blanks as a 0 instead.  I have also created a sample version of my PBI report with no sensitive data, but not sure how to share it with you as there is no option to add an attachment here that I can see.

 

As for the bridge it was a request from my boss who wants me to use Power BI to create normalized models until we get the data into a SQL environment. 

For sharing the report, the method is to save the pbix somewhere (OneDrive, etc) and share the link.

 

Measure are generally best practice for complex calculations that might need to change upon a filter change; calculated columns only change value when the data is refreshed. If you have some of your measure code in the shared file, we can look at it to see where the coding might be flawed.

Anonymous
Not applicable

Yeah I generally prefer a measure and am fully aware of the reasons to use them over calculated columns, this particular report is just giving me all kinds of issues I have never run into/been able to solve before.  Some examples of the measures I created still exist in this file so if you can figure out the reason measures are not behaving correctly as well that would be great.  However my company does not allow us to share file from OneDrive with anyone that has the link so not sure you will be able to even request access to the file, but here is the link it.  Sample PBI.  If this doesn't work maybe i can try to mail it to myself then share it from my OneDrive 

I could not access your link, so please try another method of sharing.

Anonymous
Not applicable

Ok I added it to my personal OneDrive and made it available to all with the link so this should work for you.

 

Sample Power BI Report

Something on your one drive is blocking me from getting there. I've tried on a personal computer as well to eliminate it being my company firewalls.  It is asking me to log in after I click the link.

Anonymous
Not applicable

Not sure what other would block it.  I went to the file chose the OneDrive share and selected make available to anyone with the link.  Any other suggestions for sharing the file?

Anonymous
Not applicable

@Pragati11 - can you reply in this thread with what you've found on @Anonymous's report? I still can't get it to run properly on my machine.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.