Best way to set up logical data for visualization efficiency

[unitary]

Hi

I have a data set which will get large fairly quickly.

The data has a date column, a string reference column, a couple of columns of floats and then 3 or more columns of logicals ('the logicals').

I will be 'slicing' visualizations across different date ranges and string references. With reference to visualization spead and simplicity my questions are:

1. Am I best using true/false data type or 1/0  (whole numbers in the MS world) when  summing/counting the 'true'/'1' values in the logicals?

2. I will need to combine the 3 logical columns to provide new/different column 'outcomes'.  Am i best doing this before loading into Power BI or is there no real overhead of doing the 'logical operations' e.g. OR(logical col 1, logical col 2) in Power BI? With reference back to Q1. Is this best done using a 0/1 'whole number' or true/false logical?

Thank you for your insights

Kind Regards

John

[amitchandak]

@unitary ,Can you share sample data and sample output in table format?

[unitary]

Hi Amit,

It is something like this:

The slicing will work off the data and masterRef (and possibly in the future bucketed values of int1 and int2). The masterref has multiple parents and children but initially will just drive off the parents. The dates will be bucketed daily but will range between dates. The logicals (with values 1 and 0 only) will be summed to provide the 'y-axis data' for want of a better expression against the x-axis (dates initially and possibly int1 and int2 buckets in the future). The DervivedLogicals found from logic statements e.g. OR(Logical1,Logical2),   And(Not(logical1),Logical2).

Thank you John

ddmmyyyy	MasterRef	Int1	Int2	Logical1	Logical2	DerivedLogical1	DerivedLogical1
01/11/2019	U12345	8	32	1	0	1	1
01/11/2019	U12345	10	6	0	1	0	1
01/11/2019	U12346	18	19	1	1	1	0
02/11/2019	U12345	8	35	0	1	1	0
02/11/2019	U12347	11	12	1	0	0	0
02/11/2019	U12348	19	99	1	1	0	0

[Anonymous]

Hi @unitary,

You can unpivot your logic fields to attribute and value, then you can create a line chart with date as axis, attribute a legend, value on value field with aggregate mode 'sum'.

Unpivot columns (Power Query) 

Regards,

Xiaoxin Sheng