Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dan_Wis
Frequent Visitor

Count Blanks related to different table

Hi,

 

How to count percentage of blank values having:

 

1. In one (main) table I have, apart from other coulmns: "ID". The table has appx 30 k rows.

 

IDCountry
1UK
2DE
3SE
4UK
5NO
6UK
7BE
8ES
9SE
10DK
11DE
12ES

 

2. Table2 with: single values in ID column only with non blank entries in Date column - say 6k rows

 

IDDate
101/05/2020
313/04/2019
815/06/2019
916/08/2020

 

Both tables are connected as one-to-many   Table2-MainTable

 

Based on that criteria I'm showing a bar chart with number of blank Dates, counting MainTable(ID), Axis MainTable(Country), having in filter Table2(Date) = (Blank)

 

How to count percentage of blanks, having in denominator a number of rows from MainTable? (4/12 = 33%)

 

Thank you in advance for your support on this.

1 ACCEPTED SOLUTION

@amitchandak  I've just found a way:

 

M1 = 1-DIVIDE(CALCULATE(COUNTROWS(Table),ISBLANK('Table2'[Date])),COUNTROWS(Table))
 
thanks!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Dan_Wis , Try measures like

M1 = count(Table2[Date])

Blank Dates = sumx(values(Table[ID]), if(isblank(M1]), 1,0))

@amitchandak  I've just found a way:

 

M1 = 1-DIVIDE(CALCULATE(COUNTROWS(Table),ISBLANK('Table2'[Date])),COUNTROWS(Table))
 
thanks!

THanks @amitchandak!  this is giving me a 0, as there are no blanks in table 2.

Also I would need to use other filters - seems M1 measure is not allowing to apply filters.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.