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.
Hello,
I'm trying my journey with dax and i step into issue which i can't find a solution...
I have table with CityLocation and Value and want count number of CityLocation where value is > 0.
For below result it should be 2.
I can get eaisly count of CityLocation where Value is >0 however how i can count unique City ?
CityLocation | Value |
Torotno1 | 0 |
Toronto1 | 3 |
NewYork2 | 0 |
Toronto3 | -1 |
Denver1 | 0 |
Denver2 | 1 |
Solved! Go to Solution.
@joshua3m
I can assume from your code that Value is a measure. In this case please try
COUNT =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER ( VALUES ( 'Table'[CITYLOCATION] ), [VALUE] > 0 ),
"City", LEFT ( 'Table'[CITYLOCATION], LEN ( 'Table'[CITYLOCATION] ) - 1 )
)
)
)
Hi @joshua3m
Please try
Count =
CALCULATE ( DISTINCTCOUNT ( 'Table'[CityLocation] ), 'Table'[Value] > 0 )
Hi Tamerj ,
Would this not calculate distinct only for CityLocation where i want actually look into City and not City Location combine ?
I was trying to do as below:
COUNT:
COUNTROWS(
FILTER(
ADDCOLUMNS(
VALUES(LEFT([CITYLOCATION],LEN[CITYLOCATION]-1),
"STOCK" , ([VALUE])
),
[STOCK] >0
)
)
@tamerj1 wrote:Hi @joshua3m
Please try
Count =
CALCULATE ( DISTINCTCOUNT ( 'Table'[CityLocation] ), 'Table'[Value] > 0 )
@joshua3m
I can assume from your code that Value is a measure. In this case please try
COUNT =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER ( VALUES ( 'Table'[CITYLOCATION] ), [VALUE] > 0 ),
"City", LEFT ( 'Table'[CITYLOCATION], LEN ( 'Table'[CITYLOCATION] ) - 1 )
)
)
)
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 |
---|---|
54 | |
26 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
47 | |
17 | |
12 |