Calculate number of empty in DAX

[Tito]

Hi everyone,

I would like to calculate how many names that are not in every region.

Best regards

Tito

Data

 

 
Result:

 

[Rohit11 · Accepted solution]

Hi @Tito 

Try this DAX , 

Count Blanks =

var _totalRegion = COUNTROWS(ALL('Table'[Name]))

return

(_totalRegion -

CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) ))

 Thank you. 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

[Rohit11 · Accepted solution]

Hi @Tito 

Try this DAX , 

Count Blanks =

var _totalRegion = COUNTROWS(ALL('Table'[Name]))

return

(_totalRegion -

CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) ))

 Thank you. 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

[Tito]

Hi @Rohit11 ,

thank you very much! that worked.

If a name (A,B,C..) appears several times in the Name column, what should I change in Measure. Thank you!

Best regards
Tito

[PowerBigginer]

Try this
Measure = VAR NorthNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "North"))                       VAR WestNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "West"))                         VAR SouthNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "South"))                       VAR EastNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "East")) RETURN SWITCH ( TRUE (),  'Table'[Region] = "North Not Present", NorthNotPresentCount,
                                              'Table'[Region] = "West Not Present", WestNotPresentCount,
                                              'Table'[Region] = "South Not Present", SouthNotPresentCount,
                                              'Table'[Region] = "East Not Present", EastNotPresentCount )

[PijushRoy]

Hi @Tito 

Can you please try below DAX

MEASURE =
CALCULATE (
    DISTINCTCOUNT ( 'YourTable'[Name] ),
    ISBLANK ( 'YourTable'[Region] )
)

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Tito]

hi @PijushRoy ,

unfortunately it didn't work

Thanks

Tito

 

[PijushRoy]

Hi @Tito 

As your screenshot, there are no blank region
Please share your sample data in excel format, not as Screenshot 
and expected result screenshot based on sample data

[Tito]

Hi @PijushRoy 

I have 2 columns: Name and Region

A North B West C South D East E North F West G North H North I South K East

Result:

 

[PijushRoy]

Hi @Tito 

Please find the PBIX file
https://drive.google.com/file/d/19hGo6zKDnV0GynZb39OcFlNo2_HCZ1RK/view?usp=sharing

Use the DAX for measure

MEASURE = VAR _rows = CALCULATE(COUNTROWS('Table'),ALL('Table'))
VAR _region = SELECTEDVALUE('Table'[Region])
RETURN
SWITCH(
    TRUE(),
_region = "East", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="East"),
_region = "North", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="North"),
_region = "South", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="South"),
_region = "West", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="West"))

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Tito]

hi @PijushRoy 

thank you very much! that also worked.

Column Region was just an example and we only have 4 variables, in the real Data are several variables and you can not write all in Measure manually.

If a name (A,B,C..) appears several times in the Name column, what should I change in Measure. Thank you!

Best regards
Tito

[PijushRoy]

Hi @Tito 

In my Measure, check the updated DAX

MEASURE = VAR _rows = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
VAR _region = SELECTEDVALUE('Table'[Region])
RETURN
SWITCH(
    TRUE(),
_region = "East", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="East"),
_region = "North", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="North"),
_region = "South", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="South"),
_region = "West", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="West"))

In @Rohit11 Measure, check the updated DAX

Count Blanks = 
var _totalRegion = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
return
(_totalRegion -
CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) ))

A and H respected

Thanks, @Rohit11  for your measure

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Tito]

hi @PijushRoy  @Rohit11 

Thank you both. It worked great.

If it is possible, we can edit the measure so that we are no longer shown total -2. For example with Blank().

 

Best regards
Tito

[PijushRoy · Accepted solution]

Hi @Tito 

Find the updated measure

Count Blanks = 
var _totalRegion = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
return
IF(
    HASONEVALUE('Table'[Region]),
(_totalRegion -
CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) )),
BLANK())

[Tito]

Hi @PijushRoy ,

Thank you very much! It worked really well.

Best regards
Tito