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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vibhoryadav23
Helper I
Helper I

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 I
Helper I

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 I
Helper I

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors