Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a measure that returns the cumulative total of Pro licenses each year. The data looks like the table on the left, and I want it to return the values on the right:
I’m able to create the following measure:
Cumulative Licenses =
CALCULATE (
COUNTX ( 'Power BI Licenses', [License Status] = "Pro" ),
FILTER (
'Power BI Licenses',
[Pro License Start Year]
<= MAX ( [Pro License Start Year] )
)
)
…but it won’t work in the line chart visual. I get this error:
MdxScript(Model) (4, 86) Calculation error in measure Power BI Licenses'[Cumulative Licenses]: The function COUNTX cannot work with values of type Boolean.
I tried it with COUNTAX instead of COUNTX, and it works with the visual, but doesn’t return the running totals.
Also not having luck with
Solved! Go to Solution.
Hi @ineedham ,
For your requirement, please try the measure below.
Measure = IF ( MAX ( 'Table1'[License Status] ) = "", BLANK (), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALL ( Table1 ), 'Table1'[Pro License start year] <= MAX ( 'Table1'[Pro License start year] ) && 'Table1'[License Status] <> "" ) ) )
Here is the output.
You also could have a reference of my attachment.
If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Cherry
Hi @ineedham ,
For your requirement, please try the measure below.
Measure = IF ( MAX ( 'Table1'[License Status] ) = "", BLANK (), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALL ( Table1 ), 'Table1'[Pro License start year] <= MAX ( 'Table1'[Pro License start year] ) && 'Table1'[License Status] <> "" ) ) )
Here is the output.
You also could have a reference of my attachment.
If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Cherry
That worked. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |