Multiple validation with multiple print info

[CatalinaMur]

Hi all,

Could you please help me. I have a Power BI report doing multiple validation, I created a column for each validation (more than 10) with the final result 1 or 0 (True or False if you will)

here is an example of the structure of the data:

ID	Validation 1	Validation 2	Validation 3
001	1	1	0
002	1	1	1
003	0	0	1

I would like to print all with 1, because each validation means someting is missing. I was thinking in SWICH but this is not a good one or maybe it is something that I need to consider?

[Poojara_D12]

Hi @CatalinaMur 

Create a New Column:

Add a calculated column in Power BI that checks for any 1 in your validation columns:

AnyValidationMissing = 
IF(
    'YourTable'[Validation 1] + 
    'YourTable'[Validation 2] + 
    'YourTable'[Validation 3] > 0,
    1,
    0
)

Filter Your Visual:

Add the AnyValidationMissing column to the Filters pane and set it to show only rows where AnyValidationMissing = 1.

This will ensure that your visual only shows rows where any validation failed (1).

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[sanalytics]

I agree with @Anonymous  and @Selva-Salimi  also
This is a Power query task.. No DAX.. Simply unpivot the data and create condition column in Power query.. Just like v-junyant-msft  did. You dont need any extra table.

Regards

sanalytics

[CatalinaMur]

Hi, thanks, I understand what you all agree, unpivot is a good way to summarize info... But these columns are with DAX formulas with the final result 1 or 0. I just want to try to find a way to summarize more (in power bi) to include in an email with power automate

[sanalytics · Accepted solution]

@CatalinaMur 
If your all the columns are calculated column and you are bound to create those conditions in DAX then create a calculated table.
You can use below code

output = 
VAR _1 = 
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[ID],'Table'[Validation 1] ),
"ColName","Validation 1"
)

VAR _2 = 
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[ID],'Table'[ Validation 2] ),
"ColName","Validation 2"
)
VAR _3 = 
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[ID],'Table'[ Validation 3] ),
"ColName","Validation 3"
)
VAR _tbl = 
SELECTCOLUMNS(
UNION(
	_1,_2,_3 
),"Id",[ID],"Flag",[Validation 1],"ColName",[ColName]
)
VAR _condition = 
ADDCOLUMNS(
	_tbl,"@Condition",
IF(
	[ColName] = "Validation 1" && [Flag] = 1 ,
	"Missing financial info",
 IF(
[ColName] = "Validation 2" && [Flag] = 1,
"Data are not align",
IF(
[ColName] = "Validation 3" && [Flag] = 1,
"other"
) ) ))
VAR _Result = 
ADDCOLUMNS(
 SUMMARIZE(
	'Table','Table'[ID]
 ),"Validation 1",
 MAXX(
	FILTER(
		_condition,[ID] = 'Table'[ID] &&
		[ColName] = "Validation 1"
	),[@Condition]
 ), "validation 2",
  MAXX(
	FILTER(
		_condition,[ID] = 'Table'[ID] &&
		[ColName] = "Validation 2"
	),[@Condition]
  ),
  "Validation 3",
   MAXX(
	FILTER(
		_condition,[ID] = 'Table'[ID] &&
		[ColName] = "Validation 3"
	),[@Condition]
  )
  )

	
RETURN
_Result

Below screenshot

 Attaching pbix file for your reference

https://we.tl/t-YZiZIi2yji

Hope it helps

Regards

sanalytics

[Anonymous]

Hi @CatalinaMur ,

Agree with @Selva-Salimi , I think only unpivot can meet your needs, but your post is posted in the DAX forum, and DAX may not be able to implement this step easily. You need to use Power Query to achieve:
For example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRgmEDpVgdkJgRkpghVMwYLA/BQLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Validation 1" = _t, #"Validation 2" = _t, #"Validation 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Validation 1", Int64.Type}, {"Validation 2", Int64.Type}, {"Validation 3", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Validation 1", "Validation 2", "Validation 3"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if [Attribute] = "Validation 1" and [Value] = 1 then "Missing financial info" else if [Attribute] = "Validation 2" and [Value] = 1 then "Dates are not align" else if [Attribute] = "Validation 3" and [Value] = 1 then "other" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom")
in
    #"Pivoted Column"

 Just put all of the M code into the Advanced Editor:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CatalinaMur]

Hi, happy new year, thanks!... All the validation are in DAX.. I am not complete familiar with the code that Power Query (M code, I think) but if you have a course that you could recommend me. Thanks

[Selva-Salimi]

hi @CatalinaMur 

I cant exactly understand what you mean, do you want to print validations which are 1 for each ID or you just want o prind ID's that have all valiadtion passed?!

[CatalinaMur]

Thanks to reply. I would like to print all the true in a summary column, example:

SWICH( TRUE(), Validation 1 = 1, "Missing financial info", Validation 2 = 1, "Dates are not align"........ )

I know SWICH prints the first true validation... I would like to have something similar to WHILE or FOR.

Hope this helps 🙂

[Selva-Salimi]

@CatalinaMur 

then, One of the easiest solutions that came to my mind is to unpivot your table as follows and then create a code column (which is created by concatenation of value and validation column) as follows:

then you need to create a table based on the description of each validation as follows:

(we need column named Code to be able to craete a relationship between description for those who are "1", So the code in your second table should always be "ValidationX_1").

Create a relationship between two "code" columns in this two table.

by doing this you can print each description for each ID, based on it's value. 

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.