Forum Discussion
Resolver IIHow to handle very complex IF statement
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 |
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
3 Replies
HayleyseaThanks 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
- amitchandak
Super User
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/
- v-easonf-msft
Community Support
Hi , Hayleysea
As mentioned by amitchandak , you can use function "unpivot" in query editor.
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.
