Cannot connect to Calendar

[ksab23]

Hi All, 

I have this very specific measure that I would need to display on a timeline chart (monthly, yearly). I think I know where the issue lies but I cannot fix it. First part of the division works well with the calendar, the second, where I count 'ISBLANK' doesn't. Is there a way I can count rows that are blank in different way, so I can connect it to the calendar?

PP Trained =

DIVIDE(

    DIVIDE(

    CALCULATE(

        COUNT('Oppty created'[TRN 101]),

        'Oppty created'[TRN 101] = "Valid",

        USERELATIONSHIP('Calendar '[Date], 'Oppty created'[Creation Date])

    ),

    CALCULATE(

        COUNT('ROI'[101]),

        'ROI'[Sales/NonSales] = "Sales",

        USERELATIONSHIP('Calendar '[Date], 'ROI'[101 Date])

    )

),

DIVIDE(

    CALCULATE(

        COUNT('Oppty created'[TRN 101]),

        'Oppty created'[TRN 101] = "Not Valid",

        USERELATIONSHIP('Calendar '[Date], 'Oppty created'[Creation Date])

    ),

    CALCULATE(

        COUNTROWS('ROI'),

        'ROI'[Sales/NonSales] = "Sales",

        ISBLANK('ROI'[101]),

        USERELATIONSHIP('Calendar '[Date], 'ROI'[101 Date])

    )

))

[v-jiewu-msft · Accepted solution]

Hi @ksab23 ,

First of all, many thanks to @some_bih  for very quick and effective replies.

Based on the description, please try the following dax formula by using filter function to count blank:

PP Trained =
DIVIDE(
    DIVIDE(
        CALCULATE(
            COUNT('Oppty created'[TRN 101]),
            'Oppty created'[TRN 101] = "Valid",
            USERELATIONSHIP('Calendar'[Date], 'Oppty created'[Creation Date])
        ),
        CALCULATE(
            COUNT('ROI'[101]),
            'ROI'[Sales/NonSales] = "Sales",
            USERELATIONSHIP('Calendar'[Date], 'ROI'[101 Date])
        )
    ),
    DIVIDE(
        CALCULATE(
            COUNT('Oppty created'[TRN 101]),
            'Oppty created'[TRN 101] = "Not Valid",
            USERELATIONSHIP('Calendar'[Date], 'Oppty created'[Creation Date])
        ),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'ROI',
                    ISBLANK('ROI'[101])
                )
            ),
            'ROI'[Sales/NonSales] = "Sales",
            USERELATIONSHIP('Calendar'[Date], 'ROI'[101 Date])
        )
    )
)

Create the sample table.

Create the measure to calculate blank rows.

Measure = CALCULATE(
    COUNTROWS(
        FILTER(
        'Table', ISBLANK('Table'[Closed Date]))),
    'Table'[Task] = "Task7",
    USERELATIONSHIP('Table'[Closed Date], 'Table date'[Date])
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi  based on link for ISBLANK please consider possible adjustment to your code, to spot your issue

--  ISBLANK check that an expression is strictly equal to BLANK

--  ISBLANK (x) is an alias for x == BLANK ()

@ksab23