Forum Discussion

Bali21's avatar
Bali21
Frequent Visitor
3 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
amitchandak
Super User
3 years ago

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's avatar
    Bali21
    Frequent Visitor
    3 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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