DAX formula for showing a text in the number card

[Anonymous]

Hello Everone, 

Hope you're all doing great!

In my dashboard I have a card called (Student No.) and a filter of 2 choices (Schools - Admin Building), in excel file (resource) I have a column (whole number) for how many student in each school, for admin building it will be 0 all the time. I tried to put N/A instead, but an error occured because its a number column.

what I want is a dax for whenever I select admin building, the card will show the text (N/A), but when I select schools, it will show me the number in the excel file.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create a measure to judge , if the choice is “Schools” , sum the number of students , if the choice is “Admin Building” , return the text “N/A” .

Original data :

Measure :

Measure =
VAR _Schools=CALCULATE(SUM('Table'[number]),FILTER('Table','Table'[choices]="Schools"))
return IF(SELECTEDVALUE('Table'[choices])="Schools",_Schools,"N/A")

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

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

[Anonymous]

I try to apply it on my table, it shows a message (the syntax for return is incorrect), dunno where the problem is

[ValtteriN]

Hi,

Since you only want to display either NA or a number you can circumvent the "variant type error" which you might be encountering by using functions like CONVERT, FORMAT or CONCANETATE. E.g. 

CONVERT('Table'[Value],STRING). 


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

I tried to make new column in power bi with this DAX 

Student_No= Convert( Table_name[column_name(StudentName)], String)

for the card it shows 0 no matter what I chose

[amitchandak]

@Anonymous , You can create a column based on slicer selection.

Number to text can done easily like this 

Table_name[column_name(StudentName)] & ""

For number of student you can try like

count(Table_name[column_name(StudentName)] )+0

or

countrows(filter(Table_name, Table_name[Building]<> "Admin"), Table_name[column_name(StudentName)]) +0

[Anonymous]

Thaaank you ValtteriN, but 

I think I will face a problem using it, if I convert the column to String, then it won't be able to sum students No. for schools, right ?

It will show numbers as a text, but what I want is, whenever I select School it will sum the number of stuedents, and when I select Admin Building ( by default it will sum the 0), number of students will be always 0 so once it reads 0 it will reflect a text (N/A) on the card

do I made it clear ?