Report for exceptions / errors after Refresh

[abhijit_raghava]

Hi,

I have created a dashboard by linking 2 tables say Table A (Fact Table / Transaction Table) and Table B (Dimension Table / Master Table). The data in put method for Table A is through folder extraction. Each month I will update the folder with the previous month's file / data. So when I refresh the data, Table A will be updated with latest data.

So my question is if some of the line items in Table A doesn't have any reference in Table B, it would reflect as an error. Is there any way where I can generate an exception report (in the form of a table dashboard in Power BI itself) for the line items which don't have any data links in Table B? So that I can update Table B for the line items I got in the error report / Exception Report.

For ease of understanding, the link between the tables is on Item No (unique / Primary key in Table B).

[rohit1991 · Accepted solution]

Hii @abhijit_raghava 

You can create an exception report in Power BI by identifying records in Table A that don’t have a matching Item No in Table B. Simply create a calculated column using IF(ISBLANK(RELATED(Table B[Item No])), 1, 0) and then filter a table visual where the value is 1. This will display all unmatched records, helping you easily find and update missing entries in the dimension table.

Create a calculated column in Table A:

Exception Flag = 
IF(ISBLANK(RELATED('Table B'[Item No])), 1, 0)

[v-prasare]

Hi @abhijit_raghava,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@grazitti_sapna & @rohit1991 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[grazitti_sapna · Accepted solution]

Hi @abhijit_raghava,

There are various ways to achieve it

1. Recomended (Less efforts)-> Power Query - Anti join exception table -> You can merge table A with table B, create join in Item No. Join type Left anti, this table will only have the rows which are not present in table B
2. Calculated table (Using DAX) - Create table using below DAX Exception Items =
   EXCEPT(
   VALUES(TableA[Item No]),
   VALUES(TableB[Item No])
   )
3. Add calculated column in table A, Mapping Status =
   IF(
   ISBLANK(
   RELATED(TableB[Item No])
   ),
   "Missing Mapping",
   "Mapped"
   )

Out of above recommended is that you create a separate table using Pwoer Query.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[rohit1991 · Accepted solution]

Hii @abhijit_raghava 

You can create an exception report in Power BI by identifying records in Table A that don’t have a matching Item No in Table B. Simply create a calculated column using IF(ISBLANK(RELATED(Table B[Item No])), 1, 0) and then filter a table visual where the value is 1. This will display all unmatched records, helping you easily find and update missing entries in the dimension table.

Create a calculated column in Table A:

Exception Flag = 
IF(ISBLANK(RELATED('Table B'[Item No])), 1, 0)