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

How to get Countd of ID, where ID is mapped to more than 1 location in card

Greeting Everyone,

 

I am new to the community and facing very basic issue and any help on the issue is much appreciated!!

 

I have set of data where we have ID and Location column. Now one ID can be mapped to multiple location.

I need to get Countd of ID, where ID is mapped to more than 1 location in card.

 

Below are screen shot for ref:

1. PBI screnshot

Bali21_0-1674243727476.png

2. Screen Shot of data:

IDLocationGroupDiv
1DelAPACJpn
2KolAPACJpn
3BomAPACJpn
4BlrAPACJpn
5ChnAPACAus
2BomAPACAus
4DelAPACAus
4KolAPACAus
5BomAPACAus

 

Thank you!

1 ACCEPTED SOLUTION

Hi,

 

thank you for sample data.

Sum of multi loc =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Sheet1,
            Sheet1[ID],
            "Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )
        ),
        [Multi Loc] > 1
    )
)

 

Result:

bolfri_0-1674248612491.png

I hope this is what you wanted.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Bali21
Frequent Visitor

@bolfri 

Thank you so much!! It really work 🙂

However when I tired to create another table by keeping filter "Multi Loc" > 1, It just not working and became blank. Is there any way to fix it or achieve the soltion with above scenario where one can create table by having "Multi Loc" > 1. Any help to resolve the same is much appreciated.

 

Once again thank you!!

 

Screen shot of PBI:

Bali21_0-1674491535356.png

 

 

 

 

 

 

 

 

Data:

 

Bali21_1-1674491674650.png

 

Your visual is showing data by ID and if some ID has multiloc this columns has value: 1 not the "Count of location". If you want to use this measure to filter our the rows give it the rule: "IS 1".





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




bolfri
Super User
Super User

Can you paste here this table so I don't need to reproduce it? (sample data from screen)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Bali21
Frequent Visitor

@bolfri 

 

IDLocationGroupDiv
1DelAPACJpn
2KolAPACJpn
3BomAPACJpn
4BlrAPACJpn
5ChnAPACAus
2BomAPACAus
4DelAPACAus
4KolAPACAus
5BomAPACAus

 

Let me know if you have any other question. Thank you

Hi,

 

thank you for sample data.

Sum of multi loc =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Sheet1,
            Sheet1[ID],
            "Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )
        ),
        [Multi Loc] > 1
    )
)

 

Result:

bolfri_0-1674248612491.png

I hope this is what you wanted.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.