Search code examples
countpowerbimeasure

Power BI: distinct count

I have a table Import in PowerBi of which I want to count the number of sheets per File. How do I make a measure that counts the number of sheets per file?

ColumnX ColumnY FileName SheetName
x y File1 Sheet1
a b File1 Sheet2
c d File1 Sheet3
a b File2 Sheet1
a b File2 Sheet2

How do I get this result?

File NumberOfSheets
File1 3
File2 2
Solution

  • Your measure can be like this:

    NumberOfSheets = DISTINCTCOUNT ( 'Table'[SheetName] )

    In a new table visual, add your FileName column and this new NumberOfSheets measure to produce your desired result.

    However, for the Total row, this only counts the distinct number of sheet name variations in your table. If you want the total number of sheets to be displayed in the Total row, you can change your formula to something else, like this:

    NumberOfSheets (Total Sum) = 
SUMX ( 
    VALUES ( 'Table'[FileName] ) , 
    DISTINCTCOUNT ( 'Table'[SheetName] )
)

    Here are the results:

    enter image description here

    Another edit:

    If your data table is known to have 1 row per sheet, you can easily just count rows instead:

    NumberOfSheets (Simple) = COUNTROWS ( 'Table' )

    But this depends entirely on the full data table. If this assumption holds true, there is no reason to use anything other than the COUNTROWS approach, since this is probably the faster calculation.