topic Re: Trying to filter out blank rows in a measure in DAX Commands and Tips

Trying to filter out blank rows in a measure

Steveyd78 — Sun, 21 Nov 2021 00:32:29 GMT

Ok so I have the feeling that is is really easy but it is driving me crazy ( I will paste the really simple data table at the end of this post).

My table has three columns - Date, Nae, Hours

Want to create a percentage where I divide the count of the rows of a person by the total number of rows - that isn't an issue (see below)

Now what I really want to do is for the total % for Ryan to be 40% not 20%, i.e. for the total % to NOT include the two blank rows where he didn't work. I CAN do this easily on the visalization by simply adding a filter where "Count Ryan" > 0:

Easy!! See It's now dividing by 10 not 20. Now I REALLY want to do this in a measure (so I can replicate for more people) but I have tried everything I can think of (which isn't much). The following does not work but shows the kind of thing I am trying. Intuitively I think this should be really easy but it's driving me bannanas so am asking for help!!

% Ryan days removed =

VAR Count_Ryan = CALCULATE(count(Data[Name]), Data[Name] = "Ryan")

VAR Count_Team_Total = CALCULATE(count (Data[Name]), Data[Name] <> blank())

RETURN

divide(Count_Ryan,

Count_Team_Total,

Anyway here is the data, nice and simple to cut and paste. Thanks in advance all!!

Date Name Hours

31-Mar-21	Steve	1
30-Apr-21	Steve	2
31-May-21	Steve	3
30-Jun-21	Steve	4
31-Mar-21	Ryan	1
30-Apr-21	Ryan	2
31-May-21	Bob	3
30-Jun-21	Bob	4
31-Mar-21	Steve	1
30-Apr-21	Steve	2
31-May-21	Steve	3
30-Jun-21	Steve	4
31-Mar-21	Ryan	1
30-Apr-21	Ryan	2
31-May-21	Bob	3
30-Jun-21	Bob	4
31-Mar-21	Steve	1
30-Apr-21	Steve	2
31-May-21	Steve	3
30-Jun-21	Steve	4

Re: Trying to filter out blank rows in a measure

Nathaniel_C — Sun, 21 Nov 2021 02:51:29 GMT

Hi Steveyd78 ,

Try this

Ratio = // we know that there are 20 total rows, we know that Ryans row count is 4, //We know that the distinct for all dates = 4, we know that Ryans Distinct Count = 2. So we want to get rid of the Dates proportionaly that Ryan is not listed on- therefore 4-2 = 2 and 2/4 = 50% //Therefore 20*50% becomes the denominator, while the numerator is the Ryans DistinctCount //Countrows Ryan = Calculate(COUNTROWS('HoursTable'),'HoursTable'[Name] = "Ryan") VAR _Ryan = CALCULATE ( COUNTROWS ( 'HoursTable' ), 'HoursTable'[Name] = "Ryan" ) //4 VAR _RyanDistinct = CALCULATE ( DISTINCTCOUNT ( HoursTable[Date] ), FILTER ( HoursTable, HoursTable[Name] = "Ryan" ) ) //2 VAR _allRows = COUNTROWS ( HoursTable ) //20 VAR _alldCount = DISTINCTCOUNT ( HoursTable[Date] ) //4 VAR _ratio = DIVIDE ( _alldCount - _RyanDistinct, _alldCount ) RETURN DIVIDE ( _Ryan, _allrows * _ratio )

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Trying to filter out blank rows in a measure

AlexisOlson — Sun, 21 Nov 2021 03:41:17 GMT

This should work for any of the names you pick:

% of Team Hours = VAR NameHours = SUM ( Data[Hours] ) VAR TeamHours = CALCULATE ( SUM ( Data[Hours] ), VALUES ( Data[Date] ), ALL ( Data[Name] ) ) RETURN DIVIDE ( NameHours, TeamHours )

Re: Trying to filter out blank rows in a measure

Steveyd78 — Sun, 21 Nov 2021 04:08:47 GMT

That is just awesome - am going to work through code to work out what is going on (hopefully won't take me too long!)

Re: Trying to filter out blank rows in a measure

Steveyd78 — Sun, 21 Nov 2021 04:10:28 GMT

That's amazing, really appreciated the comments - will definately help me work through the code!

Re: Trying to filter out blank rows in a measure

Nathaniel_C — Sun, 21 Nov 2021 15:58:45 GMT

Hi Steveyd78 ,

You are welcome, hope it works for you!

Nathaniel

Re: Trying to filter out blank rows in a measure

AlexisOlson — Sun, 21 Nov 2021 17:44:55 GMT

To get TeamHours, I use ALL ( Data[Name] ) to get all of the names (not just the one in the current filter context) but use VALUES ( Data[Date] ) to preserve the current Date filter context.