Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
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 ()
Proud to be a Super User!
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |