Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Any help would be appreaciated.
Thank you in advance
Solved! Go to Solution.
Hi @Anonymous,
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 )
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
In the above scenario, we have only one column (Sub Div) under rows section of a Matrix Visual. How to display "N/A" or 0 if we have 2 columns (Ex: Region and Country) under rows section of a Matrix Visual.
Hi @Anonymous,
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 )
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
Hi v-shex-msft
I have a similar problem, but insted of 0 in the blank record i need the value of previos cell value
You can refer to the quire which i have asked in other Need help forum
is there any way to replace the 0 with a "default related value" that would vary for each line (Div X for example)?
@Anonymous
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |