Forum Discussion
Solved
Table become blank after adding filter
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...
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
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