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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.