Get blank() as output

[Zakaria_1980]

Hi,

I've DB with Target for each module and how many achieved per City. so when I want to get the gap for each Module and per city, if in the DB in one of the cities, one of the modules was not forecasted to be done, i get blank() as output. how can i change that to N/A please?

Thanks

[Anonymous · Accepted solution]

Hi @Zakaria_1980 ,

It looks like a matrix. But you chose a card visual, which is puzzling.

You can use the matrix visual to show the same effect.

Here's the solution.

1.Does your data model only have one table?

If it is, use dax to create a Module table.

Table 2 = DISTINCT('Table'[Module])

2.Relationship is as follows.

3.Create two measures.

Target value = 
var _sum=SUM('Table'[Target])
return
IF(ISBLANK(_sum),"N/A",_sum)

Acieved value = 
var _sum=SUM('Table'[Achieved])
return
IF(ISBLANK(_sum),"N/A",_sum)

Then we get the results.

Check the attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zakaria_1980]

yes but it doesn't work, that's why i shared with you the DB how it looks like.

please have a look at it.

thx

[Pragati11]

HI @Zakaria_1980 ,

Just curious to know. what visual is this?

Is it a table/matrix visual? I am not able to replicate this at my end. When I try to bring element as follows on a report page in Power BI:

 Now when I start using slicer, I don't see any blanks at my end.

So now my next question is. Have you got any relationships going on in Power BI with this table?

Thanks,

Pragati

[Zakaria_1980]

this is the output that i have

[Pragati11]

HI @Zakaria_1980 ,

What measure are you moving in your card visual which is displaying BLANK() ?

Also do you have any relationships going on at your end with your table?

Thanks,

Pragati

[Zakaria_1980]

for relationships , i don't have it since i've just one Tbl.

that visual i shared with you displays blank() for one Module which is not forecatsed to be done in one city. i have one column if it's done any module =1 if not is 0, so i pick that column as data for me.

[Pragati11]

HI @Zakaria_1980 ,

Sorry I didn't understand the calculation that is giving you BLANK() here.

Can you please paste your formula for the calculation here please?

We just need to modify the calculation somehow to replace BLANK() with N/A.

Thanks,

Pragati

[Zakaria_1980]

[Pragati11]

Hi @Zakaria_1980 ,

If you want to avoid BLANK(), then you will have to create a measure to handle this.

I am assuming:

• you are moving SUM(Consolidated[Certification Applcable]) to your card visual with an additional filter on Module = "PMO Practicioner".
• Both Certification Applcable and Module columns are in same table Consolidated

Write a measure something as below:

cardCalc = 
var calc = CALCULATE(
                     SUM(Consolidated[Certification Applcable]), 
                     Consolidated[Module] = "PMO Practicioner"
                    )
RETURN
SWITCH(
       TRUE(), 
       calc = BLANK(), "N/A", calc
      )

Just check the DAX once specially for any naming conventions and filter expression values.

Move this measure to your card visual. It should display "N/A" not BLANK() now.

Thanks,

Pragati

[Zakaria_1980]

I simplified the DB as follow:

City	Module	Target	Achieved
City#1	Module#1	10	7
City#1	Module#2	10	5
City#1	Module#3	10	8
City#1	Module#4	20	16
City#1	Module#5	45	30
City#2	Module#1	26	20
City#2	Module#2	2	0
City#2	Module#3	8	7
City#2	Module#5	36	12

the output that I've is as follow.

if i select in the filter City#1, i've the following:

	Module#1	Module#2	Module#3	Module#4	Module#5
Target	10	10	10	20	45
Achieved	7	5	8	16	30

and if i select the City#2, i've the following:

	Module#1	Module#2	Module#3	Module#4	Module#5
Target	26	2	8	Blank()	36
Achieved	20	0	7	Blank()	12

what i need is that replace that blank() by N/A, please if you could implement this in Power bi desktop and share it with me.

thx

[Pragati11]

Hi @Zakaria_1980 ,

Did you try my response that I posted on Friday?

Thanks,

Pragati

[Pragati11]

Hi @Zakaria_1980 ,

You haven't shared any sample data here, so you can create a calculated column using following DAX:

IF(ISBLANK(yourTable[Column]), "N/A", yourTable[Column])

Replace yourtable and Column with your table name and column name.

Thanks,

Pragati