count blank and non blank faksp values based on Contract.

[Harish85]

HI Required a count of Blank and non blank values with contract ID, if both values are present for one contract ID that Id should not be seen in the report. for marked column there will be both blank and non blank

[Harish85]

thank you for taking the time and replying, but there are two data base tables coming from different data source, FAKSP_ecc(column) is coming from second table and Contract is coming from first table, then also it works?

[v-kongfanf-msft · Accepted solution]

Hi @Harish85 ,

Depend on your description, you can try follow steps:

1.Create a Calculated Column:

FAKSP_ECC_Status = IF(ISBLANK('Table'[FAKSP_ECC]), "Blank", "Non-Blank")

2.Create a Measure to Count Blank and Non-Blank Values:

Count_Blank = CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[FAKSP_ECC_Status] = "Blank"))

Count_Non_Blank = CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[FAKSP_ECC_Status] = "Non-Blank"))

3.Create a measure to exclude contracts with both blank and non-blank values:

Filtered_Contracts = 
IF(
    [Count_Blank] > 0 && [Count_Non_Blank] > 0,
    BLANK(),
    MAX('Table'[CONTRACT])
)

Best Regards,
Adamk Kong

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