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 All,
Need your help on the below issue.
I have created one measure - "Multi Loc = CALCULATE(DISTINCTCOUNT(Sheet1[Location](GROUPBY(Sheet1,Sheet1[Location],Sheet1[ID])))" BUT when i drag it to filter for onr of my table, everything went blank.
Could anyone help me how to resolve this. Basically I need a table which has other dimension where Multi Loc > 1.
There is another field which I created:
Screen shot for PBI:
Screen shot of Data:
ID | Name | Location | Group | Div |
1 | AB | Del | APAC | Jpn |
2 | CD | Kol | APAC | Jpn |
3 | EF | Bom | APAC | Jpn |
4 | GH | Blr | APAC | Jpn |
5 | IJ | Chn | APAC | Aus |
2 | CD | Bom | APAC | Aus |
4 | GH | Del | APAC | Aus |
4 | GH | Kol | APAC | Aus |
5 | IJ | Bom | APAC | Aus |
Solved! Go to Solution.
Hi @Bali21 ,
You can update the formula of measure [Multi Loc] as below, please find the details in the attachment.
Multi Loc =
CALCULATE (
DISTINCTCOUNT ( 'Sheet1'[Location] ),
ALLEXCEPT ( 'Sheet1', 'Sheet1'[ID], 'Sheet1'[Name] )
)
or
Multi Loc =
VAR _tab =
SUMMARIZE (
'Sheet1',
'Sheet1'[ID],
'Sheet1'[Name],
"@count",
CALCULATE (
DISTINCTCOUNT ( 'Sheet1'[Location] ),
FILTER (
ALL ( 'Sheet1' ),
'Sheet1'[ID] = EARLIER ( 'Sheet1'[ID] )
&& 'Sheet1'[Name] = EARLIER ( 'Sheet1'[Name] )
)
)
)
RETURN
SUMX ( _tab, [@count] )
Best Regards
thank you!! It works!!
@Bali21 , If you need sum try like
Sum of multi loc1 =
Sumx (
FILTER (
SUMMARIZE (
Sheet1,
Sheet1[ID],
"Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )
),
[Multi Loc] > 1
),[Multi Loc]
)
With this, you can use visual-level filter
@amitchandak Thank you for the response!!
However I miss to mention one point here. Is there any way to have "location" field as well in table where "Sum of multi loc1" >1. So my table should have ID, Name, Location which has "Sum of multi loc1" > 1.
Any help much appreciated!
Hi @Bali21 ,
You can update the formula of measure [Multi Loc] as below, please find the details in the attachment.
Multi Loc =
CALCULATE (
DISTINCTCOUNT ( 'Sheet1'[Location] ),
ALLEXCEPT ( 'Sheet1', 'Sheet1'[ID], 'Sheet1'[Name] )
)
or
Multi Loc =
VAR _tab =
SUMMARIZE (
'Sheet1',
'Sheet1'[ID],
'Sheet1'[Name],
"@count",
CALCULATE (
DISTINCTCOUNT ( 'Sheet1'[Location] ),
FILTER (
ALL ( 'Sheet1' ),
'Sheet1'[ID] = EARLIER ( 'Sheet1'[ID] )
&& 'Sheet1'[Name] = EARLIER ( 'Sheet1'[Name] )
)
)
)
RETURN
SUMX ( _tab, [@count] )
Best Regards
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |