Table become blank after adding filter

[Bali21]

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: 

Sum of multi loc =

COUNTROWS (

    FILTER (

        SUMMARIZE (

            Sheet1,

            Sheet1[ID],

            "Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )

        ),

        [Multi Loc] > 1

    )

)

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

[Bali21]

thank you!! It works!!

[amitchandak]

@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

[Bali21]

@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!

[v-yiruan-msft · Accepted 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