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
Anonymous
Not applicable

How to filter a person's name that is in less than one program?

Hi,

 

In Visualizations, I'm trying to filter those people who are only in one program.

 

For Example:

Program 1 = 100 people

Program 2 = 200 people

Program 3 = 300 people

 

John, Eric, Peter, Naomi... are in Program 1, 2, and 3. But Naomi is only in Program 2, and John is only in Program 3. So, how can I filter Naomi and John to a separate chart in Visual?

This is for a project, and I need to filter those people who have > 1 and < 1 program to be considered weather are in Inter or Intra program.

 

 

Thanks

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hello @Anonymous ,

Here is one way of doing it:

 

Summarized table =
ADDCOLUMNS (
    SUMMARIZE ( 'Table'; 'Table'[Name]; "Count"; COUNT ( 'Table'[Program] ) );
    "Inter/Intra"; IF ( [Count] > 1; "Intra"; "Inter" )
)

 This will create a seperate table that will have the column you need to filter your "Fact table".

After you generated this table you can create a relationship between Name and Name and it should work.

 

If you have anymore questions please let me know.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

View solution in original post

Anonymous
Not applicable

I'm not too sure what exactly isn't working for you. Here is my sample that might help you understand how exactly you want to apply this:

https://drive.google.com/open?id=1RHi4Szw97HkgUHaOYFPGXfAScRxHHREj

 

If you still need help please share some data and what exactly it is you want as a result (Screenshot).

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

 

 

 

 

View solution in original post

Anonymous
Not applicable

Hello @Anonymous ,

Using the table that determines if someone is inter or intra this should be pretty easy.

To calculate how many people are in more than 1 program can't you use the following measure?

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]);'Summarized table'[Inter/Intra]="Intra")

 

Same goes for people that are only assigned to 1 program

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]);'Summarized table'[Inter/Intra]="Inter")

 

This is the pbix i tried to share with you:

 

879e91433f63dab0f01783a12957fc98

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hello @Anonymous ,

Here is one way of doing it:

 

Summarized table =
ADDCOLUMNS (
    SUMMARIZE ( 'Table'; 'Table'[Name]; "Count"; COUNT ( 'Table'[Program] ) );
    "Inter/Intra"; IF ( [Count] > 1; "Intra"; "Inter" )
)

 This will create a seperate table that will have the column you need to filter your "Fact table".

After you generated this table you can create a relationship between Name and Name and it should work.

 

If you have anymore questions please let me know.

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

Anonymous
Not applicable

It says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Anonymous
Not applicable

Hello @Anonymous,

Did you use the add "Table" instead of column?

Anonymous
Not applicable

I clicked on "New Column", then enterd in the DAX and replaced the ; to ,

 

Then I try to click on "New Table" and input the DAX, it gave me this message "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

Anonymous
Not applicable

In a column this is not gonna work since we need to aggregate data. Use the add table function and create a relation between the 2 tables using the name columns.

Anonymous
Not applicable

It did work, thank you. But is not the correct output that I want. Maybe my description were bit confuse.

 

Right now this is what the new table looks like, this is correct data, but not for inter and intra:

Program 1, Count = 2000, Intra

Program 2, Count = 600, Intra

Program 3, Count = 800, Intra

Program 4, Count = 500, Intra

 

And I need to know who belongs to inter and intra. I'm really confused for how to calculate inter vs intra because our customer is too busy and didn't provide much detail for the calculation, also, I'm new to Power BI. Haha! Sorry.

 

 

 

 

Anonymous
Not applicable

I'm not too sure what exactly isn't working for you. Here is my sample that might help you understand how exactly you want to apply this:

https://drive.google.com/open?id=1RHi4Szw97HkgUHaOYFPGXfAScRxHHREj

 

If you still need help please share some data and what exactly it is you want as a result (Screenshot).

 

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

 

 

 

 

Anonymous
Not applicable

DataData

I wish I can show the data, but unfortunately, I'm not allowed, I have to censor it.

There are 4 spreadsheets total, the programs, and the authors are in two separate spreadsheets, but the authors ID's are in both spreadsheets. Right now, the only way I can think of is to use the table chart and put the author's name and the programs. And on the left side of the table, when I clicked on the program, it will filter which authors and the programs are in, but this is only showing the total of authors are in which program. My goal here is to find out which of the author is in and not in more than 1 program(s) or duplicated program, then I need to show the total of how many authors are in less than 1 and greater than 1 program(s).

Anonymous
Not applicable

Hello @Anonymous ,

Using the table that determines if someone is inter or intra this should be pretty easy.

To calculate how many people are in more than 1 program can't you use the following measure?

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]);'Summarized table'[Inter/Intra]="Intra")

 

Same goes for people that are only assigned to 1 program

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Name]);'Summarized table'[Inter/Intra]="Inter")

 

This is the pbix i tried to share with you:

 

879e91433f63dab0f01783a12957fc98

Anonymous
Not applicable

Hi @Anonymous 

 

Capture.PNG

I guess I have the rules wrong. If you look at the screenshot, that's the rules I need to follow to define Inter and Intra.

It also has to go by no just programs but titles as well. If the person with the same title but in the different program will be considered Inter, and if the person with the same titles and the same programs will be considered Intra. Sometimes it will have two same programs and mixed with other programs but with the same title that will be count as both Inter and Intra.

 

There are 500 different titles and 10 programs only. Usually, when I filtered the title, they should always be the same title, and the only difference is that it will show different programs.

I'm working on an author's published report. This is why I need to find how the Inter and Intra this way.

Can you please help me with the formula (Dax)? And I really appreciate all the help and your time.

Thanks

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.