Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I’m trying to count the number of blanks in a sequence of numbers, however, where the sequence encounters multiple blanks I only want to count this multiple blanks once.
E.g.
Col A
=====
1
2
Blank()
Blank()
5
6
Blank()
Blank()
9
10
In the above table, I only want to return the number of blanks counted = 2.
Solved! Go to Solution.
The power of Excel worksheet formula is beyond your imaginaiton,
Iteration is way much easier in PQ than in Excel,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyBJMIwghMGqOJmqLxnZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Val = _t]),
Gaps = let l={"dummy"}&Source[Val] in List.Accumulate({0..List.Count(l)-2}, 0, (s,c) => if Text.Length(l{c})>0 and Text.Length(l{c+1})=0 then s+1 else s)
in
Gaps
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi,
Again, assuming columns named Column1 and Index:
MyMeasure :=
CALCULATE(
COUNTROWS(
FILTER(
Table1,
ISBLANK( Table1[Column1] )
&& NOT (
ISBLANK( LOOKUPVALUE( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
)
)
)
)
Regards
Hi @Jos_Woolley
Clean and simple 👍 I like it 👌
will it count the blank if it was in the first row?
Also can please help me understand the need for CALCULATE?
Guilty on both counts! 😊 No need for CALCULATE and it doesn't work if the first entry is blank.
Back to the drawing board...
Yes I've been there too 😅
please check up my solution and let me know your openion
Your code should work perfectly with little modification
MyMeasure =
VAR UniqueBlanksTable =
FILTER (
Table1,
ISBLANK ( Table1[Column1] )
&& NOT ISBLANK ( LOOKUPVALUE ( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
)
VAR UniqueBlanksCount =
COUNTROWS ( UniqueBlanksTable )
VAR FirstPotentialBlankRowInFilter =
FILTER (
Table1,
ISBLANK ( Table1[Column1] )
&& Table1[Index] = MIN ( Table1[Index] )
)
VAR Result =
IF (
COUNTROWS ( FirstPotentialBlankRowInFilter ) = 0,
UniqueBlanksCount,
UniqueBlanksCount + 1
)
RETURN
Result
Thanks, tamerj1.
Having looked at it again, I'm wondering why I can't just amend it to simply:
MyMeasure :=
COUNTROWS(
FILTER(
Table1,
ISBLANK( Table1[Column1] )
&& (
Table1[Index] = 1
|| NOT (
ISBLANK( LOOKUPVALUE( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
)
)
)
)
Guess I'm missing something obvious?
Thanks for all your input - much appreciated.
Regards
Actually that should work. Good job 👍
I will try it later with different senarios but as I said I believe it should work.
@Jos_Woolley But use
Table1[Index] = MIN (Table1[Index] ) instead of Table1[Index] = 1 to keep it dynamic with the filter context. But I think you need to store it in a variable outside filter.
Hi @noneother
Here is my solution using a measure https://www.dropbox.com/t/MY1X01Z4Z1sqyD8h
Index row is a must have. This code should work wherever the location of the gap is (at the top, bottom, midle)of the table) and no matter the number of blank rows is in each gap. You can even filter the table on whatever conditions you want and the number of gaps will change accordingly.
Number of Gaps =
VAR BlankTable =
FILTER (
'Table',
ISBLANK ( 'Table'[Col A] )
)
VAR NewIndexTable =
ADDCOLUMNS (
BlankTable,
"@NewIndex", RANKX ( BlankTable, 'Table'[Index], 'Table'[Index], ASC )
)
VAR IndexDifferenceTable =
ADDCOLUMNS (
NewIndexTable,
"@IndexDifference", 'Table'[Index] - [@NewIndex]
)
VAR DifferenceColumn =
SELECTCOLUMNS (
IndexDifferenceTable,
"@Difference", [@IndexDifference]
)
VAR Result = COUNTROWS ( DISTINCT ( DifferenceColumn ) )
RETURN
Result
Hi @noneother ,
Here's my solution.
1.Create an index column.(You can add it in the power query or using dax).
2.Create the first measure to move the value of column1 one row down.
Measure = CALCULATE(MAX('Table'[Column1]),FILTER(ALLSELECTED('Table'),[Index]=MAX('Table'[Index])-1))
Remember to turn on "Show items with no data", otherwise the data will be incomplete.
3.Create the second measure to get the blank value(de-duplicated).
Measure 2 = IF(MAX('Table'[Column1])=[Measure],1)
4.Create the last measure to sum the [Measure 2].
COUNT = SUMX(ALL('Table'),[Measure 2])
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The power of Excel worksheet formula is beyond your imaginaiton,
Iteration is way much easier in PQ than in Excel,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyBJMIwghMGqOJmqLxnZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Val = _t]),
Gaps = let l={"dummy"}&Source[Val] in List.Accumulate({0..List.Count(l)-2}, 0, (s,c) => if Text.Length(l{c})>0 and Text.Length(l{c+1})=0 then s+1 else s)
in
Gaps
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello @noneother
Just clarifying the problem: Do you want to count the number of "blocks" of consecutive blank values, irrespective of the values of the numbers surrounding them?
If so, we would need an additional index column to define the ordering of the rows. Can we assume that an index column exists?
e.g.
Col A | Index |
1 | 1 |
2 | 2 |
blank | 3 |
blank | 4 |
5 | 5 |
6 | 6 |
blank | 7 |
blank | 8 |
9 | 9 |
10 | 10 |
Alternatively, is this like the classic islands/gaps problem where a "gap" corresponds to missing values, and in this example there are two gaps because 3-4 and 7-8 are missing?
Regards,
Owen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |