Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am stuck a simple problem.
Here is my example:
I have a table with event.title and registrant.email.
I added a columns based on the event.title column session names it tags them program1 or 2
Based on the email address I add a column which parse out the domain name. The individual user id will be removed eventually for privacy reasons.
A user may attend a session multiple time and they can also attend sessions from either programs.
event.title | registrant.email | Program | domain |
session1 | email1@domain1.com | program1 | domain1 |
session1 | email2@domain1.com | program1 | domain1 |
session1 | email1@domain2.com | program1 | domain2 |
session1 | email1@domain5.com | program1 | domain5 |
session2 | email1@domain1.com | program1 | domain1 |
session2 | email2@domain1.com | program1 | domain1 |
session2 | email1@domain2.com | program1 | domain2 |
session2 | email1@domain3.com | program1 | domain3 |
session2 | email1@domain1.com | program1 | domain1 |
session2 | email1@domain5.com | program1 | domain5 |
session3 | email1@domain1.com | program2 | domain1 |
session3 | email2@domain1.com | program2 | domain1 |
session3 | email1@domain2.com | program2 | domain2 |
session3 | email1@domain3.com | program2 | domain3 |
session3 | email1@domain1.com | program2 | domain1 |
session3 | email1@domain4.com | program2 | domain4 |
session4 | email1@domain1.com | program2 | domain1 |
session4 | email2@domain1.com | program2 | domain1 |
session4 | email1@domain2.com | program2 | domain2 |
session4 | email1@domain3.com | program2 | domain3 |
session4 | email1@domain1.com | program2 | domain1 |
session4 | email1@domain4.com | program2 | domain4 |
What I am trying to achieve.
Get a list of domains which attended program1 or 2 only
Get a list of domains which have attended sessions both from program1 and 2
I don't need counts of the domains which participated in both programs, but I need a list of domains rather.
From the example above domain5 only attended program1 sessions while domain1-4 have have attended sessions from both progams.
What would be a good approach to solve this problem?
Thanks!
Solved! Go to Solution.
Hi @Laszlo
I see the next solution.
First add new calculated table
Table 2 = DISTINCT(SELECTCOLUMNS('Table';"Domain";'Table'[domain];"Program";'Table'[program]))
Next add measure to Table 2 like this
Measure = calculate(DISTINCTCOUNTNOBLANK('Table 2'[Program]);ALLEXCEPT('Table 2';'Table 2'[Domain]))
Then you can create visuals for example
So, if Measure = 2, this domain is including both program, if measure=1 - the only.
P.S. in your dataset example i see not only domain5, but domain4 also has attented session from the only program2
hi @Laszlo
exactly!
but if to be more correct - new table contains distinct pair domain-program: we are clearing origin table from duplicate pairs. If in your datasource such duplicates are impossible - distincttable is not neccessary step
Hi @Laszlo
I see the next solution.
First add new calculated table
Table 2 = DISTINCT(SELECTCOLUMNS('Table';"Domain";'Table'[domain];"Program";'Table'[program]))
Next add measure to Table 2 like this
Measure = calculate(DISTINCTCOUNTNOBLANK('Table 2'[Program]);ALLEXCEPT('Table 2';'Table 2'[Domain]))
Then you can create visuals for example
So, if Measure = 2, this domain is including both program, if measure=1 - the only.
P.S. in your dataset example i see not only domain5, but domain4 also has attented session from the only program2
Is there a JavaScript solution / override for that? 😀
I am using the PowerBI-JavaScript SDK,
the custom visual hierarchy slicer works just fine.
But when i have try to use the Tuple filter in the JS SDK for (multi columns selection) it turns out it is not really supported.
slicer_Tuple_Filter = {
$schema: "http://powerbi.com/product/schema#tuple",
target: [
// same table different columns selection
{table: "Table A", column: "column A"},
{table: "Table A", column: "column B"} // same table different columns selection
],
filterType: 6, // can be int 6 or string 'Tuple'
operator: "In",
// value for selected columns
values: [
[ {value: "111"}, // value for column A
{value: "222"}// value for column B
]
]
};
Thank you! 🙂
here is the lick again:
Thanks very much AZ38!
I, implmented and it and got it working, but just so I understand what is happening.
The new table contains the distinct domains in my source table as well as the distinct programs.
The measure I am not 100% sure how it works. It calculates the distinct without blanks entry of the the program column, so far I am good. The ALLEXCEPT fuction ignores all the filters on the domains column and only apply the filter as it relates to programs.
Thnaks,
Laszlo--
hi @Laszlo
exactly!
but if to be more correct - new table contains distinct pair domain-program: we are clearing origin table from duplicate pairs. If in your datasource such duplicates are impossible - distincttable is not neccessary step
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.