cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## how to distinct count based on lower granularity than shown in powerbi visual

I’m having trouble “thinking in DAX” WRT counting distinct stores based on a filter condition (catch - stores are not part of the visual canvas)

I have a table, sadly cant show the real data here due to privacy concerns , it is something like this [Table name - Company]

 Company Name Store name Accepted commands Total commands A AA 1 1 A AA 1 1 A AA 0 1 A AB 1 1 A AB 1 1 A AB 1 1 A AC 1 1 A AC 1 1 A AC 0 1 B BA 1 1 B BA 1 1 B BA 1 1 B BA 0 1 B BB 1 1 B BB 1 1 B BB 0 1 B BC 1 1 B BC 1 1 B BC 1 1

I want to produce a visualization of a table that goes as follows [It can have only three columns as shown below]
{I calculated a measure 'Command acceptance' = sum(accepted commands)/sum(total commands) - this is on company level

 Company Name Command Acceptance count Stores with acceptance < 0.73 A 0.77 2 B 0.8 1

Problem - I want to calculate the third column "count stores with acceptance < 0.73"
This column should distinct count the stores whose command acceptance is less than 0.73 (accepted/total)
In the example show abouve,
Company A has two stores namely, "AA" and "AC" whose command acceptance is less than 0.73
Company  B has 1 store namely , "BB" whose command acceptance is less than 0.73

Third column should distinct count on store level of command acceptance.

What i tried ----->

CALCULATE(DISTINCTCOUNT(Company[store_name]),FILTER(company,[Command_Acceptance] < 0.73))

It gives me
 Company Name Command Acceptance count Stores with acceptance < 0.73 A 0.777777778 3 B 0.8 3

What am i doing wrong ? Any help is greatly appreciated.

2 ACCEPTED SOLUTIONS
Solution Specialist

You should summarize the values of both company/store againist Command Acceptance then count the row or records.

``````Count Stores with acceptance < 0.73 =
COUNTROWS (
FILTER (
SUMMARIZE ( Company, Company[Company Name], Company[Store name] ),
"StoreCount", [CommandAcceptance]
),
[CommandAcceptance] < 0.73
)
)``````

Check this sample file

Solution Sage

Here you've got it. The parameter Threshold can be made dynamic.

``````[Stores with CA < Threshold] = // CA - command acceptance
var Threshold = .73
var Result =
SUMX(
SUMMARIZE(
Company,
Company[Company Name],
// This grouping wouldn't be needed
// if Stores had a unique StoreID.
// but I have to assume that it's not
// so and only what you've shown is
// actually what you work with.
Company[Store Name]
),
CALCULATE(
var AcceptedComs =
SUM( Company[Accepted Commands] )
var TotalComs =
SUM( Company[Total Commands]
var Result_ =
AcceptedComs < Threshold * TotalComs
return
DIVIDE( Result_, Result_ )
)
)
return
Result``````
2 REPLIES 2
Solution Sage

Here you've got it. The parameter Threshold can be made dynamic.

``````[Stores with CA < Threshold] = // CA - command acceptance
var Threshold = .73
var Result =
SUMX(
SUMMARIZE(
Company,
Company[Company Name],
// This grouping wouldn't be needed
// if Stores had a unique StoreID.
// but I have to assume that it's not
// so and only what you've shown is
// actually what you work with.
Company[Store Name]
),
CALCULATE(
var AcceptedComs =
SUM( Company[Accepted Commands] )
var TotalComs =
SUM( Company[Total Commands]
var Result_ =
AcceptedComs < Threshold * TotalComs
return
DIVIDE( Result_, Result_ )
)
)
return
Result``````
Solution Specialist

You should summarize the values of both company/store againist Command Acceptance then count the row or records.

``````Count Stores with acceptance < 0.73 =
COUNTROWS (
FILTER (
SUMMARIZE ( Company, Company[Company Name], Company[Store name] ),
"StoreCount", [CommandAcceptance]
),
[CommandAcceptance] < 0.73
)
)``````

Check this sample file

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors