Forum Discussion
Post PatronGet blank() as output
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
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.
12 Replies
- Pragati11
Super User
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
Zakaria_1980I 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
- 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
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.
