cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Mahi1827
Resolver I
Resolver I

How to replace empty cells to zero (0) in matrix column values using direct mode.

i am using direct query, 2 columns product service, product status and below is sample data


product service, product status
phase 1               open
phase 2               started
phase 3                waiting
phase 4                comlpleted

 

when i use this data into matrix i got below view, assume there are 30 open rows for phase 1,20 started rows for phase 2, 10 waiting rows for phase 3
and 05 completed rows for phase 4 then my matrix shows like below

 

product statusop    open, started, waiting , comlpleted
product service 
phase1                   30
phase 2                                25
phase 3                                                 20
phase 4                                                                 05


in above matrix view i am getting empty values when there is no prodcuts compare with other phases, here i would like to show 0
when there is no prodcuts assigned or there is no data (null or blank)

 

in matrix rows are product service
columns are product status
values are count(product service)

 

my expected output like below
product status open started waiting comlpleted
product service
phase 1             30        0          0               0
phase 2              0        25         0                0
phase 3              0        0          10               0
phase 4              0        0            0             0
phase 4             0         0            0             05

 

i have tried with checking by isblank() like below
emptystatus= var test=calculate(count(table[product status])) return if(emptystatus= blank(),0,emptystatus)

and tried with diffrent options but still i am unable to show 0 where there is no data associated when the data is transorm into matrix.

 

Please look into this and provide your inputs. Thank you

 

Thank you

Mahi1827

1 ACCEPTED SOLUTION

Hi @Mahi1827 ,

I had a look at your file and have updated the file as you require.

Download the file here

 

Appreciate your kudo!! Mark this post as solution if this works for you.


Nikhil
Hit the Thumbs-up button if you find this informative!!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!

View solution in original post

6 REPLIES 6
NikhilKumar
Community Champion
Community Champion

This can  e achieved by adding +0. A sample dax is below, create something like this

Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))

 

Appreciate your kudos.

Mark my post as solution if this helps.


Nikhil
Hit the Thumbs-up button if you find this informative!!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!

Hi NikhilKumar,

Thank you for your response and time

 

Actually i have tried with given logic but its look like still am getting blank instead of 0.
here is my updated dax.

Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))


statuscount=CALCULATE(count(table(productservice)+0,'Table'[product service] in DISTINCT('Table[[product service]))

here is how the data look like for some example

 

product status product service
open                 phase1
open                 phase1
open                  phase1
completed         phase 2
completed          phase 2
completed          phase 2
completed          phase 2

 

after adding data into matrix :

Product status         open    completed
product service
phase1                      3
phase2                                      4

 

in above example i would like show 0 when there is no data associated with rows and columns

my expected output like below:


Product status           open          completed
product service
phase1                       3                      0
phase2                       0                      4

 

rows iam using as product service , columns using as Product status and values given as count(product service)
here to avoid blanks i have used adding +0 in count(product service),tried with comparing length of the count and also tried with new logic suggested by you. but still iam not able to show 0 intstead of blank .

 

please check and suggest your ideas. Thank you.

 

Thanks,

Mahi 1827

Hi @Mahi1827 ,

 

If possible can you share pbix removing sensitive data or a sampke pbix?

 

Appreciate your kudos!! 


Nikhil
Hit the Thumbs-up button if you find this informative!!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!

 

Hi Nikhil,

 

Thanks for your time, please check the sample file below. I have tried with both measure and measure 2 but still i am not able to show 0 instead of blank vallues. 

 

https://1drv.ms/u/s!AlARJq6xd5KedVlP0zJpo2xgG_s?e=tR2Okt

 

Please share your ideas and solution. Thank you.

 

Thanks,

Mahi1827

Hi @Mahi1827 ,

I had a look at your file and have updated the file as you require.

Download the file here

 

Appreciate your kudo!! Mark this post as solution if this works for you.


Nikhil
Hit the Thumbs-up button if you find this informative!!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!

Hi Nikhil,

 

This logic is working as expected.Thank you so much for your time and help on this.

 

Thanks,
Mahi

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors