Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hayleysea
Resolver II
Resolver II

How 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:

IDV1V2V3V4V5V6V7
A1  11  
B1111 1 

 

Visual:

 

A

V1: 1

V4: 1

V5: 1

B

V1: 1

V2: 1

V3: 1

V4: 1

V6: 1

1 ACCEPTED SOLUTION
Hayleysea
Resolver II
Resolver II

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

View solution in original post

3 REPLIES 3
Hayleysea
Resolver II
Resolver II

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
Community Support
Community Support

Hi , @Hayleysea 

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

14.png

15.png

16.png

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
Super User
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/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.