Forum Discussion

CatalinaMur's avatar
CatalinaMur
Regular Visitor
1 year ago
Solved

Multiple validation with multiple print info

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) ...
  • sanalytics's avatar
    sanalytics
    1 year ago

    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's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    CatalinaMur
    Regular Visitor
    1 year ago

    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