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

Show items with no data and flag it with text instead of blank

Hello community. 

I am once again stump as to how I could approach this issue. I need to identify and display where data is invalid please see example below. 

Table1:

IDValue
1A
2B
3C
4D


Table2:

IDValue
1A
1A
2B
2B
4D
4D


Desired outcome:

IDValue
1A
1A
2B
2B
3No value in table 2. Only in table 1.
4D
4D


I know the obivous solution would be to merge two tables and create a calculated column and evaluate if column Value is blank put "No value in table 2. Only in table 1." else return Value.

Nature of my dataset large so this is not feasible as it would take too long to refresh. Is there any other way to do this with a one to many cardinality relationship? So far I am using the show item with no data and it is showing the ID correctly. But I am not sure how to not show the blank but rather flag it as an issue to report to my manager that people are entering data incorrectly.  



2 REPLIES 2
VahidDM
Super User
Super User

Hi @Anonymous 

 

Create a new Table with the following DAX expression:

Table = 
VAR _A =
    SUMMARIZE (
        EXCEPT ( 'Table 1', 'Table 2' ),
        [ID],
        "Value", "No value in table 2. Only in table 1."
    )
VAR _B =
    UNION ( 'Table 2', _A )
RETURN
    _B

 

Output:

 

VahidDM_0-1649373868931.png

Sample file attached, please download.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Anonymous
Not applicable

Hi, 

just tried this, i get error that my table doesnt have the same number of column. Is there any other options?

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.