cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Resolver I
Resolver I

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 @ladyhaley 

 

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/

 

 

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors