Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How to make distinct count value with filter as co...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to make distinct count value with filter as constant

08-12-2022
11:42 PM

Hello,

Need help. Here's my sample data.

I have derived this measure to count distinct by Metrics_ID in my sample dataset, which will give me 12. I want the count to show as 12 on every row as my denominator (Unique count by Metrics_ID) instead of 1 on every row. How can i achieve that in DAX Measure?

**Denominator =**

**VAR Count_PCM_denominator=CALCULATE(DISTINCTCOUNT('rep F_ITS_MetricsLanding'[Metrics_ID]),FILTER('rep F_ITS_MetricsLanding','rep F_ITS_MetricsLanding'[Metrics_ID] in {"PCM01","PCM03","PCM04","PCM09","PCM10","PCM19","PCM20","PCM23","PCM33","PCM34","PCM35","PCM36"}),ALL('rep F_ITS_MetricsLanding'[Metrics_ID],'rep F_ITS_MetricsLanding'[LBU]))**

**RETURN Count_PCM_denominator**

**SAMPLE DATA:**

LBU | Metrics_ID | Avg Compliance % |

EILUX | PCM23 | 60.10% |

PAMC | PCM01 | 78.80% |

PAMC | PCM03 | 81.30% |

PAMC | PCM04 | 62.10% |

PAMC | PCM09 | 56.80% |

PAMC | PCM10 | 59.60% |

PAMC | PCM19 | 68.60% |

PAMC | PCM20 | 55.90% |

PAMC | PCM23 | 85.90% |

PAMC | PCM33 | 65.00% |

PAMC | PCM34 | 47.80% |

PAMC | PCM35 | 57.10% |

PAMC | PCM36 | 62.00% |

PCALKK | PCM04 | 100.00% |

**EXPECTED RESULTS:**

Expected Results for EILUX | ||||

LBU | Metrics_ID | Avg Compliance % | Denominator (unique by Metrics_ID) | Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12 |

EILUX | PCM23 | 60.10% | 12 | 5.00% |

Expected Results for PCALKK | ||||

LBU | Metrics_ID | Avg Compliance % | Denominator (unique by Metrics_ID) | Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12 |

PCALKK | PCM04 | 100.00% | 12 | 8.33% |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
04:29 AM

Hey @Georgia_H ,

I changed the denominator and the division measure in my example. Please be aware that I only used **two** metrics inside both measures to create a virtual table.

The denominator now is more or less a constant:

```
denominator =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return metricsCount
```

You might expand the definition of the denominator like so

```
denominator check =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return
IF( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
, metricsCount
, BLANK()
)
```

The division measure now looks like this:

```
division =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return
AVERAGEX(
'Table'
, if( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
, DIVIDE( 'Table'[Avg Compliance %] , metricsCount )
, BLANK()
)
) * 100
```

The table visual looks like this:

Of course, it's possible to create a table from the 12 metrics, then you do not have to repeat the definition in both measures, but if they are just used in two measures I would probably use the measure definition.

As the metric is used inside your table, it will contribute to the current filter context, but the current filter context will not be expanded, for this reason the DISTINCTCOUNT will return 1.

Hopefully, this will help to tackle your challenge

Regards,

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
04:29 AM

Hey @Georgia_H ,

I changed the denominator and the division measure in my example. Please be aware that I only used **two** metrics inside both measures to create a virtual table.

The denominator now is more or less a constant:

```
denominator =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return metricsCount
```

You might expand the definition of the denominator like so

```
denominator check =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return
IF( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
, metricsCount
, BLANK()
)
```

The division measure now looks like this:

```
division =
var metricsToCount = {"PCM01" , "PCM23"}
var metricsCount = COUNTROWS( metricsToCount )
return
AVERAGEX(
'Table'
, if( FIRSTNONBLANK( 'Table'[Metrics_ID] , 'Table'[Metrics_ID] ) in metricsToCount
, DIVIDE( 'Table'[Avg Compliance %] , metricsCount )
, BLANK()
)
) * 100
```

The table visual looks like this:

Of course, it's possible to create a table from the 12 metrics, then you do not have to repeat the definition in both measures, but if they are just used in two measures I would probably use the measure definition.

As the metric is used inside your table, it will contribute to the current filter context, but the current filter context will not be expanded, for this reason the DISTINCTCOUNT will return 1.

Hopefully, this will help to tackle your challenge

Regards,

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
08:37 PM

Hi @TomMartens

Just a quick question, instead of hardcoding the list of Metrics in the var below, possible to check through the list of Metrics_ID that present in the table? Reason being is that some new Metrics_ID may not have data for any particular period hence, they should not be counted as 1 as my constant denominator. With this syntax, i am always getting the count of Metrics_ID hardcoded. Thank you.

var metricsToCount = {"PCM01" , "PCM23"}

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
11:34 PM

Hey @Georgia_H ,

It's difficult to provide a solution that suits your needs if you do not describe your requirements completely. Make sure that the sample data you provide represents your data model (tables, relationships, calculated columns, and measures). Consider creating a pbix file that contains sample data, upload the pbix file to onedrive or dropbox and share the link. if you are using Excel to create the sample data instead of the manual input method share the xlsx as well.

Starting with the question on how to have

- DISTiNCTCOUNT overcome the current filter context,
- to there are metrics available that should not be considered and now
- if metrics that should be considered have no values throughout any period (there is
**no period**in your sample data) these metrics should be removed from the table.

It's not a good idea to change the direction of the initial question as this can confuse other users as these users will not consider this thread to find an answer to their problem.

It's better to start a new thread instead.

The general approach to finding a solution for your problem might look like this (I'm pretty sure that I miss important information):

- define a table with all possible metrics (maybe a dimension table in your star schema data model)
- reduce the table by removing metrics based on rules like "a metric that has no values in any period" has to be removed, maybe this rule will transform to no value in the current year
- use the found table to count the denominator and inside the division.

In regards to your remark that you will get the count of metrics hardcoded if you used this approach:

`var metricsToCount = {"PCM01" , "PCM23"}`

At the time of providing my solution I was *inspired* by your approach from further down this thread without knowing that metrics might be excluded due to business rules:

`,,, in {"PCM01","PCM03","PCM04","PCM09","PCM10","PCM19","PCM20","PCM23","PCM33","PCM34","PCM35","PCM36"} ...`

Regards,

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
12:18 AM

Hey @Georgia_H ,

maybe these two measures provide what you are looking for:

denominator (basically a distinctcount of the metrics_id column, with ALL (the table) )

```
denominator =
CALCULATE(
DISTINCTCOUNT( 'Table'[Metrics_ID] )
, ALL( 'Table' )
)
```

And the division ( this one is more complex as I'm using the table iterator function AVERAGEX to calculate the average after the division)

```
division =
AVERAGEX(
'Table'
, DIVIDE( 'Table'[Avg Compliance %] , [denominator] )
) * 100
```

This allows to create a table visual like the one below:

Regards,

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
12:34 AM

HI @TomMartens

Thanks for the quick response. The issue is for those with multiple rows like PAMC in my data set, if you select on PAMC, it will still show as 1 on each row, instead of 12 on every row. That is the issue i am facing. Also, i have a filter on certain metrics_ID for my denominator.

My expected result should be:

Expected Results for PAMC | ||||

LBU | Metrics_ID | Avg Compliance % | Denominator (unique by Metrics_ID) | Expected Compliance_Value (AVG) divided by MAX Count of Denominator i.e. 12 |

PAMC | PCM01 | 78.80% | 12 | 6.57% |

PAMC | PCM03 | 81.30% | 12 | 6.78% |

PAMC | PCM04 | 62.10% | 12 | 5.18% |

PAMC | PCM09 | 56.80% | 12 | 4.73% |

PAMC | PCM10 | 59.60% | 12 | 4.97% |

PAMC | PCM19 | 68.60% | 12 | 5.72% |

PAMC | PCM20 | 55.90% | 12 | 4.66% |

PAMC | PCM23 | 85.90% | 12 | 7.16% |

PAMC | PCM33 | 65.00% | 12 | 5.42% |

PAMC | PCM34 | 47.80% | 12 | 3.98% |

PAMC | PCM35 | 57.10% | 12 | 4.76% |

PAMC | PCM36 | 62.00% | 12 | 5.17% |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
12:44 AM

Hey @Georgia_H ,

this is how my data looks:

Here you will find my pbix https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EUMsIS3jsJBAt6dpaI5hngsB9wwHO...

Regards,

Tom

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-13-2022
01:20 AM

Hi Tom,

I tested the concept in my real dataset.

My real dataset has more than 12 Metrics_ID, however i only need to count on 12 Metrics_ID, hence my measure is written like:

ALCULATE(DISTINCTCOUNT('rep F_ITS_MetricsLanding'[Metrics_ID]),FILTER('rep F_ITS_MetricsLanding','rep F_ITS_MetricsLanding'[Metrics_ID] in {"PCM01","PCM03","PCM04","PCM09","PCM10","PCM19","PCM20","PCM23","PCM33","PCM34","PCM35","PCM36"}),ALL('rep F_ITS_MetricsLanding'[Metrics_ID]))

I dont get the same results as yours (12 on every row). I still get 1 on the relevant rows. What could be wrong with my DAX syntax?

Featured Topics

Top Solution Authors

User | Count |
---|---|

190 | |

83 | |

75 | |

74 | |

51 |

Top Kudoed Authors

User | Count |
---|---|

170 | |

91 | |

88 | |

80 | |

73 |