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 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:

IDNameLocationGroupDiv
1ABDelAPACJpn
2CDKolAPACJpn
3EFBomAPACJpn
4GHBlrAPACJpn
5IJChnAPACAus
2CDBomAPACAus
4GHDelAPACAus
4GHKolAPACAus
5IJBomAPACAus

 

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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