cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAX_User07 New Member
New Member

Replacing blank cells in Matrix visuals

 

Hi,

I have created matrix visual .When I plot the matrix,row wise I am getting blank cells as per matrix because there is no data corresponding to cell.,I want to replace empty cells  with "No value" in power bi

find attached details.

TestData.PNG

 

 

 

 

 

 

Any help would be appreaciated.

Thank you in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Replacing blank cells in Matrix visuals

Hi @DAX_User07,

 

You can refer to following steps to achieve your requirement.

 

1. Add new table to extract sub div value from original table.

 

T3 = VALUES(T2[Subdiv])

2. Use original table div and new table subdiv to create a matrix visual.
3. Write a measure to calculate corresponding value and replace blank records as zero and drag to matrix visual value field.

 

 

M1 =
VAR result =
    CALCULATE (
        SUM ( T2[Value] ),
        FILTER (
            ALLSELECTED ( T2 ),
            [Div] IN VALUES ( T2[Div] )
                && [Subdiv] IN VALUES ( 'T3'[Subdiv] )
        )
    )
RETURN
    IF ( result <> BLANK (), result, 0 )

15.png

 

 

Notice:

1. you can replace '0' in if statement as any value. if you try to use text value replace , measure result type will convert as text which not able to direct used in other calculation.

2. themistoklis's solution works for existed blank records(e.g. A,3, ), if these blank records not real existed in your table, it not works.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
themistoklis New Contributor
New Contributor

Re: Replacing blank cells in Matrix visuals

@DAX_User07

 

You cant actually put a text on a number field.

So instead of 'No Value" you should put zeros.

 

A nice workaround is to create a new Measure with the following formula:

 

Measure = SUM(Table[Value]) + 0
Community Support Team
Community Support Team

Re: Replacing blank cells in Matrix visuals

Hi @DAX_User07,

 

You can refer to following steps to achieve your requirement.

 

1. Add new table to extract sub div value from original table.

 

T3 = VALUES(T2[Subdiv])

2. Use original table div and new table subdiv to create a matrix visual.
3. Write a measure to calculate corresponding value and replace blank records as zero and drag to matrix visual value field.

 

 

M1 =
VAR result =
    CALCULATE (
        SUM ( T2[Value] ),
        FILTER (
            ALLSELECTED ( T2 ),
            [Div] IN VALUES ( T2[Div] )
                && [Subdiv] IN VALUES ( 'T3'[Subdiv] )
        )
    )
RETURN
    IF ( result <> BLANK (), result, 0 )

15.png

 

 

Notice:

1. you can replace '0' in if statement as any value. if you try to use text value replace , measure result type will convert as text which not able to direct used in other calculation.

2. themistoklis's solution works for existed blank records(e.g. A,3, ), if these blank records not real existed in your table, it not works.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |