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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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