Forum Discussion

PuneethERB's avatar
PuneethERB
Frequent Visitor
6 years ago
Solved

Power BI DAX No Data Calculation

Hi All,

[Referring to the screenshot] About the data: I've prepared the model where in the 'Date' as seen in the screenshot is an intermediate table and 'Code' is also an intermediate table and the Measures 'Visits Total' & 'IA visits','Vacation Visits' are from different tables. These tables primary dates and codes columns are connected to the intermediate table.

Requirement: if (any of the measure has no data/0 then i want the result as 'Count the respective date as 1 day' ) the result i wanted is highlighted in the RED color in screenshot.

Hoping for the DAX help on this.Would be grateful for the help in this regard. Thanks.

Need Help
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi PuneethERB ,

     

    You could create two measures.

    Measure =
VAR a =
    PRODUCTX (
        FILTER (
            'Table',
            'Table'[Value] IN { BLANK (), 0 }
                && (
                    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 0
                )
        ),
        1
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[Type] ) ) && ISINSCOPE ( 'Table'[Date] )
            && SELECTEDVALUE ( 'Table'[Value] ) IN { BLANK (), 0 },
        a,
        SUM ( 'Table'[Value] )
    )
    Measure new = 
SUMX(VALUES('Table'[Date]),[Measure])

    Then put "Measure new" into Values.

    Here is the result.

    Here is my test file for your reference.

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi PuneethERB ,

     

    You could create two measures.

    Measure =
VAR a =
    PRODUCTX (
        FILTER (
            'Table',
            'Table'[Value] IN { BLANK (), 0 }
                && (
                    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 0
                )
        ),
        1
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[Type] ) ) && ISINSCOPE ( 'Table'[Date] )
            && SELECTEDVALUE ( 'Table'[Value] ) IN { BLANK (), 0 },
        a,
        SUM ( 'Table'[Value] )
    )
    Measure new = 
SUMX(VALUES('Table'[Date]),[Measure])

    Then put "Measure new" into Values.

    Here is the result.

    Here is my test file for your reference.