Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bali21
Frequent Visitor

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:

Bali21_1-1674527645348.png

 

Screen shot of Data:

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

 

1 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] )

yingyinr_0-1674614054023.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Bali21
Frequent Visitor

thank you!! It works!!

amitchandak
Super User
Super User

@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_0-1674530457848.png

 

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

 

Bali21_0-1674578988672.png

 

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] )

yingyinr_0-1674614054023.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.