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

     

CatalinaMur's avatar
CatalinaMur
Regular Visitor
1 year ago

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's avatar
Selva-Salimi
Solution Sage
1 year ago

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.