Replace blank values in table visual for records with existing category

[IgnatiusLoyola]

Good day,

I am working on creating a Power Bi report that shows users usage of licenses that they have. I also added a date filter to show usage throughout time periods. However, to avoid confusing of users of thinking that a certain person does not have a license, I created a measure to show 0 for usage if an individual did not have any usage history during the filtered period. My issue is that now it is showing usage for all license types/categories, does anyone have any suggestions so it only shows usage for license type they have assigned to them? Below is my data model and measure I created.

In the example below, all users only have a Visio license but it is still showing usage for the other categories.

UsageFormatted =

var vSeconds= IF(ISBLANK(SUM(SnowLicenseManager[TotalUsageSeconds])), 0, SUM(SnowLicenseManager[TotalUsageSeconds]))

var vMinutes=int(vSeconds/60)

var vRemainingSeconds=FORMAT(MOD(vSeconds, 60), "00")

var vHours=INT(vMinutes/60)

var vRemainingMinutes=FORMAT(MOD(vMinutes,60), "00")

var vDays=FORMAT(INT(vHours/24), "00")

var vRemainingHours=FORMAT(MOD(vHours,24), "00")

return

  vDays&":"&vRemainingHours&":"&vRemainingMinutes&":"&vRemainingSeconds

[nielsvdc · Accepted solution]

Hi @IgnatiusLoyola,

In the measure you need to check if a user has the filtered license assigned. If not, you should return blank, otherwise return the amount of usage time. Something like the measure below

UsageFormatted =
VAR vSecondsRaw =
    SUM ( SnowLicenseManager[TotalUsageSeconds] )

// Check whether the current User + License combination is assigned,
// independent of whether there is usage in the selected date range
VAR HasLicenseAssigned =
    CALCULATE (
        COUNTROWS ( UserLicenseAssignment ),
        REMOVEFILTERS ( SnowLicenseManager )
    ) > 0

// Only show 0 when assigned; otherwise return BLANK so unassigned licenses disappear
VAR vSeconds =
    IF ( HasLicenseAssigned, COALESCE ( vSecondsRaw, 0 ), BLANK () )

VAR vMinutes = INT ( vSeconds / 60 )
VAR vRemainingSeconds = FORMAT ( MOD ( vSeconds, 60 ), "00" )
VAR vHours = INT ( vMinutes / 60 )
VAR vRemainingMinutes = FORMAT ( MOD ( vMinutes, 60 ), "00" )
VAR vDays = FORMAT ( INT ( vHours / 24 ), "00" )
VAR vRemainingHours = FORMAT ( MOD ( vHours, 24 ), "00" )
RETURN
    IF (
        ISBLANK ( vSeconds ),
        BLANK (),
        vDays & ":" & vRemainingHours & ":" & vRemainingMinutes & ":" & vRemainingSeconds
    )

Hope this helps. If so, please give kudos ‌‌👍 and mark as Accepted Solution ‌‌✔️ to help others. If you resolved your question, let us know what worked for you.

[IgnatiusLoyola]

Hi @nielsvdc, thank you very much! This did the trick, I just had to change the REMOVEFILTERS table from SnowLicenseManager to the DateDimension table.