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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Calculate Count using a measure that uses moving parameter

Data is mainly indicators and year of publication - table SDG.

I have a table called 'Old' that makes the % of availability change, because it considers only X years old.

On the pbix I upload here I have a page with a table with Country and %.

I need to know:

- Number of countries with % < 25

- Number of countries with % between 25 and 50

- Number of countries with % between 50 and 75

- Number of countries with % > 75

 

And these numbers will move when the threshold year (Old value) moves, so it has to be a measure.

Can you figure it out? Is it even possible? Please help!

 

Pbix File link

Capture.JPG

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @Anonymous ,

 

as your measure always needs a filtercontext, you have to create that table internally in the measure and then count the rows with the desired criteria.

The following should work for the 25%:

025 = 
-- Creates a table with Geographic area and the % measures
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
-- Then you filter that table to the rows where % < 0.25
VAR vFilterdTable = FILTER ( vBaseTable, [@%] < 0.25 )
RETURN
    -- And then you sum the amount of rows that are left
    SUMX ( vFilterdTable, 1 )

 

Then you have to add a second filer criteria for the amount 25-50%:

2550 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.25 && [@%] < 0.5 )
RETURN
    SUMX ( vFilterdTable, 1 )

 

And repeat the same for 50-75:

5075 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.5 && [@%] < 0.75 )
RETURN
    SUMX ( vFilterdTable, 1 )

 

And last but not least or > 75%:

75 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.75)
RETURN
    SUMX ( vFilterdTable, 1 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thanks for your solutions! I appreciate it!

How would you do to count the empty ones on the 0-25 as well? @selimovd @FrankAT 

I tried to add:

VAR vFilterdTable = FILTER ( vBaseTable, or([@%] < 0.25, ISBLANK([@%]) )) but it does not seem to work...

Hey @Anonymous ,

 

that was close. But you have to add it to the vBaseTable as in the filtered the blanks already won't exist.

The following should work:

025 = 
-- Creates a table with Geographic area and the % measures including blanks
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", IF([%] <> BLANK(), [%], BLANK())
    )
-- Then you filter that table to the rows where % < 0.25 or BLANK
VAR vFilterdTable =
    FILTER ( vBaseTable, [@%] < 0.25 || [@%] = BLANK() )
RETURN
    -- And then you sum the amount of rows that are left
    SUMX ( vFilterdTable, 1 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @Anonymous ,

 

Please try to modify your  [Count of INDICATOR not older]:

 

Count of INDICATOR not older = var a= 
CALCULATE(DISTINCTCOUNT('SDG'[INDICATOR]), FILTER(SDG,'SDG'[how old] < MAX(Old[Old])+1 )
)
return if(ISBLANK(a),0,a)

 

Then use the following measure:

 

Number of countries with % < 25 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]<=0.25),SDG[Geographic area])+0

Number of countries with %between 25 and 50 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>0.25&&[_%]<=0.5),SDG[Geographic area])+0

Number of countries with %between 50 and 75 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>=0.5&&[_%]<0.75),SDG[Geographic area])+0

Number of countries with %between >75 = var a =SUMMARIZE(SDG,SDG[Geographic area],"_%",[%]) return COUNTX(FILTER(a,[_%]>=0.75),SDG[Geographic area])+0

 

 

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

 

Best Regards,

Dedmon Dai

FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

you can do it with a disconnected table which holds the buckets:

 

10-07-_2021_20-23-16.png

 

The report view looks like this:

 

10-07-_2021_20-21-58.png

 

Take a look at the attached PBIX file.

 

selimovd
Super User
Super User

Hey @Anonymous ,

 

as your measure always needs a filtercontext, you have to create that table internally in the measure and then count the rows with the desired criteria.

The following should work for the 25%:

025 = 
-- Creates a table with Geographic area and the % measures
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
-- Then you filter that table to the rows where % < 0.25
VAR vFilterdTable = FILTER ( vBaseTable, [@%] < 0.25 )
RETURN
    -- And then you sum the amount of rows that are left
    SUMX ( vFilterdTable, 1 )

 

Then you have to add a second filer criteria for the amount 25-50%:

2550 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.25 && [@%] < 0.5 )
RETURN
    SUMX ( vFilterdTable, 1 )

 

And repeat the same for 50-75:

5075 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.5 && [@%] < 0.75 )
RETURN
    SUMX ( vFilterdTable, 1 )

 

And last but not least or > 75%:

75 = 
VAR vBaseTable =
    ADDCOLUMNS (
        VALUES ( SDG[Geographic area] ),
        "@%", [%]
    )
VAR vFilterdTable = FILTER ( vBaseTable, [@%] >= 0.75)
RETURN
    SUMX ( vFilterdTable, 1 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.