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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vibhoryadav23
Helper II
Helper II

Assign a value to missing matches between two tables

Hi,

 

I am pulling values from Table_2 in Table_1 with a realtionship. But there are some missing values in Table_2 which shows blank values in my Table_1. Instead of blank, I want to hard code a number eg - '8' or '0' whenever there is a missing match.

 

Table_1

User

A

B
C
D
E
F

 

Table_2

Usercount
A1
C5
E4

 

Expected output (Hardcoding '8' in this example)

UserCount
A1
B8
C5
D8
E4
F8

 

Thanks in advance.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can substitute something else for blanks like this:

Count =
VAR _Count = SUM ( Table_2[count] )
RETURN
    IF ( ISBLANK ( _Count ), 8, _Count )

 

View solution in original post

4 REPLIES 4
vibhoryadav23
Helper II
Helper II

Ignore this for now as I have found a workaround for it (By creating a new table with unique values).

 

Thanks for your help!

vibhoryadav23
Helper II
Helper II

Hi Alexis,

 

This work well with some minor changes. But it always shows all the vales even after I apply a filter. Is there a way to filter values with the respective filters. 
For example - If I add a new column with user attributes, like age group and I filter on that, it still shows all the values.

It can be tricky since you have to specify which non-existing values you do and don't want to replace (how do you tell which blanks are which?). Can you give a specific example of what you're getting versus what you expect to get?

AlexisOlson
Super User
Super User

You can substitute something else for blanks like this:

Count =
VAR _Count = SUM ( Table_2[count] )
RETURN
    IF ( ISBLANK ( _Count ), 8, _Count )

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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