Need help to write DAX code for the below Query

[sagarsahoo_123]

Hello Team,

As i am new to this DAX code, i need your support to write the code for the below requirement. I have posted the sample table to make it simpler to understand.

I have a table having three column and i need to calculate sum of Column value A but the filter has to be applied on column B and C for specific requirement. 

Column A	Col B	Col C
1	A
0.75	B	9
1	C
1		9
0.75		9.75
1

In the above column i need the sum value of Column A when Col B is having "Blank" value and Col C is having number value.

Here the result will be 1.75 as if you look at the 2nd and 3rd last row in which Col B is having blank cells and Col C is having Value as well. Please note Col B is having Text value where as Col C is filled with Numerical value.

Any help on DAX code is highly appreciated.

Many Thanks in Advance 

Regards,

Sagar

[v-jianboli-msft · Accepted solution]

Hi @sagarsahoo_123 ,

This issue may caused by space value and blank value. Please check your Source data in Power Query. You can use Replace Value in Power Query.

Here is a simple example:

As you can see in the images, the two values are different in Text data type.

You can use Replace Value:

Final output:

Best Regards,

Jianbo Li

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

[sagarsahoo_123]

Hello Team,

Thanks a lot for  such detailed explanation and quick support. It helped to get the desired result. Really appreciated. 

Regards,

Sagar

[v-jianboli-msft]

Hi @sagarsahoo_123 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[sagarsahoo_123]

Hi Team,

I tried with the method you suggested and but if you look at the May 2022 data which i am attaching the PBIX file link here, it is showing only value 1.

Please see the Card Visulaization in the Details Page where DigiACT HC is showing 1 for the may 2022 and i look at the data for May month there are two types of blank cells. One is (Blank) mentioned cell and other is blank cell.

If you look at the data, you will get an idea.

https://1drv.ms/u/s!AqNLfMSghxwyhgY6NBS9rDX05xon?e=iAdj4Y

Please download the PBIX file from the above link and suggest how this blank information of "Nippon Status" column can be corrected!

If you look at the Card formula, three columns are used like FTE,Nippon Status,Digiact hrs. Need to calculate the Sum(FTE) based on Blank value of Nippon status and Values of Digiact hrs.

Thanks a lot for your quick support.

Regards,

Sagar

[johnt75]

try

Col A total =
CALCULATE (
    SUM ( 'Table'[Column A] ),
    ISBLANK ( 'Table'[Column B] ),
    NOT ISBLANK ( 'Table'[Column C] )
)