Forum Discussion

ksab23's avatar
ksab23
Icon for Helper I rankHelper I
2 years ago
Solved

Cannot connect to Calendar

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])
    )
))
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.