How to show 0 dynamically in matrix when there is no data or blank in the direct query mode

[Mahi1827]

Hi,

Here is how the data look like for scenario with some example and also copied sample pbx file link below

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

(product id is unique), please cehck sample file for more deatils.

sample data:

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.

I have tried with both measure1 and measure 2 show in sample pbix file but still i am not able to show 0 instead of blank values. 

Measure1 = CALCULATE(count(Sheet1[product service ])+0,Sheet1[product service ] in distinct(Sheet1[product service ]))

Measure2 = var blankcheck=calculate(count(Sheet1[product service ])) return if(blankcheck=blank(),0,blankcheck+0)

please check and suggest your ideas. Thank you.

Thanks

Mahi1827

[Pragati11 · Accepted solution]

Hi @Mahi1827 ,

Create 2 measures:

Completed =

var com = CALCULATE(COUNT(productTest[Product status]), productTest[Product status] = "completed")

RETURN

IF(com = 0, 0, com)

Open =

var op = CALCULATE(COUNT(productTest[Product status]), productTest[Product status] = "open")

RETURN

IF(op = 0, 0, op)

Then move these measure along with your Product service column on the matrix visual:

Thanks,

Pragati

[Mahi1827]

Hi Pragati,

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

Thanks,
Mahi

[themistoklis]

@Mahi1827 

Try adding zero at the end of the formula. Outside CALCULATE function

Measure1 = CALCULATE(count(Sheet1[product service ])+0,Sheet1[product service ] in distinct(Sheet1[product service ])) + 0