Forum Discussion
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
Community 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]))