How to handle very complex IF statement

[Hayleysea]

Hi there,

Here is my problem:

I have form that submits values into a table which is then used as my data source. This form has 50+ variables and not all of them will be filled in in any one submission. I need to produce a visualisation that lists each submission and only has values that aren't blank. Because there are so many variables the only way I can think of is to write an if statement for every possible combination of non blank values to bring into my visual in a calculated column.

Is there a better way to do this?

Example:

ID	V1	V2	V3	V4	V5	V6	V7
A	1			1	1
B	1	1	1	1		1

Visual:

A	V1: 1 V4: 1 V5: 1
B	V1: 1 V2: 1 V3: 1 V4: 1 V6: 1

[Hayleysea · Accepted solution]

Thanks @v-easonf-msft  and @amitchandak for your responses. 

The way I have ended up doing it is to instead use if statements that look for blank values, and if it's blank then to show nothing (""), if theres a value then add it in. For example 

Combinevalues(" ", If(isblank([v1]), "", "V1: " & [v1] & unichar(10) & unichar(10)),  If(isblank([v2]), "", "V2: " & [v2]& unichar(10) & unichar(10)) etc. )

This is working to meet my needs, I just had to repeat it for each column in my calculated column

[v-easonf-msft]

Hi , @Hayleysea 

As mentioned by @amitchandak , you can use function "unpivot"  in  query editor.

sample pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hayleysea, If V1 to V7 some member of a dimension, you can unpivot it

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/

And use as Dimension/reference Table

Or If they are measure or columns, You can Use Show on Row in Matrix .

Refer :

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/