Forum Discussion

GJLY's avatar
GJLY
Frequent Visitor
1 year ago
Solved

Matrix: Show blanks as 0

Hi, I have the following measure: KPI_GeneralMeasure_AC_FC_BU = SWITCH(     SELECTEDVALUE(_selection_Series[Selection], "FAKTISK"),     "FAKTISK",[KPI_Actual],     "PROGNOSE",[KPI_Forecast],...
Need Help
  • GJLY's avatar
    GJLY
    1 year ago

    Hi,
    thank you all for the suggestions.

    Unfortunately, none of them worked, but I did manage to figure out the solution.
    In essence, one has to create a virtual table of all possible dimension combinations in the given filter context first, and then add 0 to these. This is necessary because some of the combinations simply don't exist in the data, and you can't add 0 to a datapoint that doesn't exist in the first place. The matrix visual plays a trick on you if you will, as it will show a cell that corresponds to a certain combination, but this datapoint doesn't really exist.

    Here's the DAX code I used that works:

    KPI_GeneralMeasure_AC_FC_BU =
    var _ValueCombinationsToCheck =
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES( Dim_Date[Year Month] ),
            "Value",[KPI_Forecast] --sufficient to only check this one because it contains both actuals (for historical periods) and forecast for future periods. Budget should be without background color anyways
        ),
        ALLSELECTED(Dim_Date)
    )

    --Generates a virtual table with all possible combinations for the current date filter context. The idea is to check whether there is at least one value for some period in the current filter context, in order to then add +0 for those periods where there is no value, so that these show up as 0 in the matrix and will be affected by conditional formatting.
     
    var _ShowValue = NOT ISEMPTY( FILTER( _ValueCombinationsToCheck, [Value] <> 0 ) )
    --True if at least one value <>0 is found in the current filter context. If no value is found, we want to ignore/not show the entire row anyway (and not add +0)
     
    RETURN
    SWITCH(
        SELECTEDVALUE( _selection_Series[Selection], "FAKTISK" ),
        "FAKTISK",IF(_ShowValue,[KPI_Actual]+0,[KPI_Actual]),
        "PROGNOSE",IF(_ShowValue,[KPI_Forecast]+0,[KPI_Forecast]),
        "BUDSJETT",[KPI_Budget],
        BLANK()
    )



GJLY's avatar
GJLY
Frequent Visitor
1 year ago

Hi,
thank you all for the suggestions.

Unfortunately, none of them worked, but I did manage to figure out the solution.
In essence, one has to create a virtual table of all possible dimension combinations in the given filter context first, and then add 0 to these. This is necessary because some of the combinations simply don't exist in the data, and you can't add 0 to a datapoint that doesn't exist in the first place. The matrix visual plays a trick on you if you will, as it will show a cell that corresponds to a certain combination, but this datapoint doesn't really exist.

Here's the DAX code I used that works:

KPI_GeneralMeasure_AC_FC_BU =
var _ValueCombinationsToCheck =
CALCULATETABLE(
    ADDCOLUMNS(
        VALUES( Dim_Date[Year Month] ),
        "Value",[KPI_Forecast] --sufficient to only check this one because it contains both actuals (for historical periods) and forecast for future periods. Budget should be without background color anyways
    ),
    ALLSELECTED(Dim_Date)
)

--Generates a virtual table with all possible combinations for the current date filter context. The idea is to check whether there is at least one value for some period in the current filter context, in order to then add +0 for those periods where there is no value, so that these show up as 0 in the matrix and will be affected by conditional formatting.
 
var _ShowValue = NOT ISEMPTY( FILTER( _ValueCombinationsToCheck, [Value] <> 0 ) )
--True if at least one value <>0 is found in the current filter context. If no value is found, we want to ignore/not show the entire row anyway (and not add +0)
 
RETURN
SWITCH(
    SELECTEDVALUE( _selection_Series[Selection], "FAKTISK" ),
    "FAKTISK",IF(_ShowValue,[KPI_Actual]+0,[KPI_Actual]),
    "PROGNOSE",IF(_ShowValue,[KPI_Forecast]+0,[KPI_Forecast]),
    "BUDSJETT",[KPI_Budget],
    BLANK()
)



  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi GJLY ,

    Thank you for sharing your update and confirming that you dont have any issue. Please let us know if you need any further assistance.

    Thanks again for your contribution!