Forum Discussion
Generate calculated table containing rows where count of a value is greater 0 for each user
Memorable MemberIt would be easier for me to revert to SQL logic rather than DAX for something like this. I would:
a. created a calculated table: TableFalse that is just the false records
b. created a calculated table: TableTrue that is just the true records
c. do an outer join on the ID field of TableFalse to TableTrue so all False records result
.....in this resulting table some rows will have the TableTrue fields as blanks
d. create a calculated table: NoMethod that filters out the records with blank TableTrue fields (only 1 field is needed)
this might not get you quite there - - air code and haven't completely thought thru every possible variation.....but will get you close
am pretty sure I forgot to include Distinct.... you'll want 1 record per ID in each table true/false......
I've created two tables with something like the following:
calc_conf_false = FILTER('TABLE_A','TABLE_A'[confirmed]=FALSE())
calc_conf_false = FILTER('TABLE_A','TABLE_A'[confirmed]=TRUE())
But when I attempt to perform a left outer join I get an 'No common join columns detected' error. They have the same number of columns.
calc_joined_conf_falsetrue = NATURALLEFTOUTERJOIN(calc_conf_false,calc_conf_true)
Now here's where I'm treading into newish territory. I can do this via SQL but I haven't created a join as you mentioned in PowerBI before, so I'm not quite certain how it's automatically trying to join these tables togther.
I'm tackling both approaches on this thread since I can see cases where I need to break out data in Power BI that comes from non-relational structures. So in this case, I can see breaking out multiple tables from one. Am I totally on the wrong path the way I'm creating the calculated tables to begin with, perhaps?