Variant Data type to show blank values to NA.

[Anonymous]

Hello,

I am trying to use as calculated column DAX as below,

Count_loading_col =SWITCH(TRUE(),

'TATLOADING'[Hour]=BLANK(),BLANK(),

'TATLOADING'[Hour]<4,COUNT('TATLOADING'[Hour]),

'TATLOADING'[Hour]>4,0).

Step 2:

Total_loading = DIVIDE('TATLOADING'[Count_loading_col],COUNT('TATLOADING'[Hour]))

Step 3:

IF(ISBLANK(TATLOADING[Total_loading]),"ND",CONVERT(TATLOADING[Total_loading],STRING)).

At Step 1, I am performing operations to check the count, else put blank.

Step 2, Based on the count I am performing divide operation.

Step3: After that I am putting ND in non-blank cells.

My requirement is

1. I need to put ND in non blank cells, for that I need to convert the whole number to text. If I convert

count operations is not performing.

2. After converting to Text also some cells are not displaying ND. Please find the image.

Any help on these two points.

Thanks.

[harshnathani]

Hi @Anonymous ,

Try

Column =
IF (
    TATLOADING[Total_loading]
        = BLANK (),
    "ND",
    CONVERT (
        TATLOADING[Total_loading],
        STRING
    )
)

If not working,

Share some sample data pls and expected output.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks for your response @harshnathani .

I have a column called Hour. It can be from 0 to 100, This was data.

Step 1: I performed switch DAX to calculatef following condition,

If any row is blank put it as ND(which is not supporting) so I used blank().

If hour<4, I need to count how many rows are >4.

If hour>4, need to put the result as zero. (not achieved the target).

SWITCH(TRUE(),

'TATLOADING'[Hour]=BLANK(),BLANK(),

'TATLOADING'[Hour]<4,COUNT('TATLOADING'[Hour]),

'TATLOADING'[Hour]>4,0)

Step 2: After that to calculate the percentage I divide overall count vs count which is less 4(from step 1).

DIVIDE('TATLOADING'[Count_loading_col],COUNT('TATLOADING'[Hour]))

Step 3: In order to convert the blank to ND. I am performing following DAX.

IF(ISBLANK(TATLOADING[Total_loading]),"ND",CONVERT([Total_loading],STRING))

Here there are two things,

1. If I convert the total_loading which is summing the overall count, will it affect the overall result.

2. For some blank cells "ND" is not appearing.

Can you help above two things?

Note: I tried to put the blank cells to ND in step 1 itself but since both are same datatype it is

not taking into consideration.

[harshnathani]

Hi @Anonymous ,

Share some sample data pls or a sample pbix file.

Regards,

HN

[Anonymous]

Hour	Weekname	Count_loading_col	Total_loading	Total teus	Location_name	Column
41	20WK24	0	0	90		0
4	20WK24			80		ND
	20WK24				a	ND
8	20WK24	0	0	90		0
71	20WK24	0	0	80	b	0
3	20WK24	22	1	90		1
21	20WK25	0	0	90		0
13	20WK25	0	0	88		0
	20WK25					ND
4	20WK25				c	ND
41	20WK25	0	0		d	0
7	20WK25	0	0			0
	20WK25					ND
47	20WK25	0	0	80	b	0
11	20WK25	0	0	80		0
23	20WK25	0	0		e	0
9	20WK25	0	0		b	0
15	20WK25	0	0		a	0
8	20WK25	0	0		a	0
2	20WK25	22	1			1
5	20WK25	0	0			0
	20WK25					ND
50	20WK25	0	0	90		0
16	20WK25	0	0		a	0
6	20WK25	0	0		c	0
6	20WK25	0	0		a	0

This was my sample data.

I am using data across locations and different weeks. I understand that for some location, data was not recorded as either ND or any value. so its not showing anything. Please look into the screenshot below.  But how can I put all blank values as "ND" if I filter using location also.

Thanks.

[Anonymous]

Power BI is not Excel. You cannot mix data types in a column. If you want to do that, all entries in the column must be text. But if you do that, you will not be working with numbers, only with text that LOOKS LIKE a number, so you won't be able to calculate unless you convert inline. But this is NOT how Power BI works and not how things should be written if they are to be correct. It'll slow down calculations and make logic complex, hard to maintain.

Also, it appears that the logic you're trying to implement does not correspond to what you've written. COUNT(table[column]) put in a column calculates the total count without any conditions. From your description it appears this is not what you really want.

Best
D