topic Re: Multiple validation with multiple print info in DAX Commands and Tips

Multiple validation with multiple print info

CatalinaMur — Sun, 29 Dec 2024 20:09:13 GMT

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?

Re: Multiple validation with multiple print info

Selva-Salimi — Sun, 29 Dec 2024 22:42:50 GMT

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?!

Re: Multiple validation with multiple print info

CatalinaMur — Sun, 29 Dec 2024 22:56:16 GMT

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 🙂

Re: Multiple validation with multiple print info

Selva-Salimi — Mon, 30 Dec 2024 00:19:51 GMT

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.

Re: Multiple validation with multiple print info

Anonymous — Tue, 31 Dec 2024 02:37:30 GMT

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.

Re: Multiple validation with multiple print info

sanalytics — Tue, 31 Dec 2024 12:33:18 GMT

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

Re: Multiple validation with multiple print info

Poojara_D12 — Wed, 01 Jan 2025 07:22:08 GMT

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

Re: Multiple validation with multiple print info

CatalinaMur — Wed, 01 Jan 2025 23:39:52 GMT

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

Re: Multiple validation with multiple print info

CatalinaMur — Wed, 01 Jan 2025 23:46:58 GMT

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

Re: Multiple validation with multiple print info

sanalytics — Thu, 02 Jan 2025 15:24:15 GMT

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