Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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:
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.
It says "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Hello @Anonymous,
Did you use the add "Table" instead of column?
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."
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.
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.
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.
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).
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:
Hi @Anonymous
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
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |