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

     

sanalytics's avatar
sanalytics
Super User
1 year ago

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

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's avatar
    sanalytics
    Super User
    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