Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ksab23
Helper I
Helper I

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])
    )
))
1 ACCEPTED SOLUTION
v-jiewu-msft
Community Support
Community Support

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.

vjiewumsft_0-1713861850629.png

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])
)

 

vjiewumsft_2-1713861924159.png

 

 

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.

View solution in original post

2 REPLIES 2
v-jiewu-msft
Community Support
Community Support

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.

vjiewumsft_0-1713861850629.png

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])
)

 

vjiewumsft_2-1713861924159.png

 

 

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.