Forum Discussion

Hussain's avatar
Hussain
Icon for Helper I rankHelper I
5 years ago
Solved

Measure to filter columns

Hi

 

I have a database table with a column named multimedia type which has 3 values - Video,Image,Other.

I want to create a Power BI Table for the category Video only.

I want to display the Multimedia File Name, Uploader Name, FileLength(in secs).

I don't want to  use Power BI filters. So, I create a measure like so - 

Total videos length (sec):=
CALCULATE (
SUM ( VaultAssets[FileLength] ),
FILTER ( VaultAssets, VaultAssets[VideoImageOther] = "Videos" )
)

When I put this measure as a column (alonng with other variables I mentioned above) in the Power BI table, I do get rows for only Videos. But the problem is that the actual database table (the source table) has Blanks for FileLength and this measure only displays rows with non blank FileLength values. I also tried other measures like - 

Video Length(Secs):=SUMX(FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"),VaultAssets[FileLength])

 

VL:=
VAR fl = CALCULATE(SUM(VaultAssets[FileLength]),FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"))
Return
IF(fl=BLANK(),0,fl)

 

:=IF(CALCULATE (
SUM ( VaultAssets[FileLength] ),
FILTER ( VaultAssets, VaultAssets[VideoImageOther] = "Videos" )
) = BLANK(),0,

CALCULATE (
SUM ( VaultAssets[FileLength] ),
FILTER ( VaultAssets, VaultAssets[VideoImageOther] = "Videos" )
)

)

 

None of them gives correct number of rows

 

Summing it up: I want to create a measure for only Videos which when put in a power BI table visual, gives me all the rows including blanks.

 

 

  • If you just want number of rows, use COUNTROWS: 

     

    Videos =COUNTROWS(FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"))

     

    Otherwise, to replace blank with zero, try: 

     

    Video Length(Secs):=SUMX(FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"),IF(ISBLANK(VaultAssets[FileLength]), 0, VaultAssets[FileLength]))

1 Reply

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion

    If you just want number of rows, use COUNTROWS: 

     

    Videos =COUNTROWS(FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"))

     

    Otherwise, to replace blank with zero, try: 

     

    Video Length(Secs):=SUMX(FILTER(VaultAssets,VaultAssets[VideoImageOther]="Videos"),IF(ISBLANK(VaultAssets[FileLength]), 0, VaultAssets[FileLength]))