DAX looking for a value in a table stored in a variable

[Anonymous]

Hello,

The code below has been simplified (filters, etc.), I might look useless but that's what I'm looking for.

1st step, I'm storing stuff in a variable table :

VAR _taches_GMAOPS_ = Selectcolumns (
        'gmaops_WORKORDER_WOACTIVITY'
        ;"origine"          ; "GMAOPS"
        ;"chantier"         ; gmaops_WORKORDER_WOACTIVITY[WO.SITEWONUM]
        ;"tache_gmaops"     ; gmaops_WORKORDER_WOACTIVITY[WA.SITEWONUM]
        ;"statut"           ; gmaops_WORKORDER_WOACTIVITY[WA.STATUS]
    )

2nd step, I'd like to get data from another table based on the ID stored in my VAR table :

Var _DI_GMAOPS_ = Selectcolumns (
	FILTER(
		'gmaops_WORKORDER_WOACTIVITY'
		; gmaops_WORKORDER_WOACTIVITY[WA.SITEWONUM] IN {SUMMARIZE(_Tache_GMAOPS_filtrees_; [tache_gmaops])}
	)
	; "Origine"          ; "GMAOPS"
	; "Chantier"         ; gmaops_WORKORDER_WOACTIVITY[WO.WONUM]
	; "Phase"            ; gmaops_WORKORDER_WOACTIVITY[WA.SITEWONUM]
	; "Statut_phase"     ; gmaops_WORKORDER_WOACTIVITY[WA.STATUS]
	; "Date_phase"       ; gmaops_WORKORDER_WOACTIVITY[WA.REPORTDATE]
)

 The problem is on the : IN SUMMURAZE, I'm getting and error message : "Une table de plusieurs valeurs a été fournie alors qu'une valeur unique est attendue" meaning that he's getting a table of values and not a single value.

How I filter my main table on the ID that are stored in a column of my VAR table please ?

[Anonymous]

That wasn't really clear maybe... Sorry about that...

This is my input table :

DI		tache	statut
---		-----	------
TA1		1		ok
TA1		2		ok
TA1		3		ok
TA1		4		ko
TA2		5		ok
TA2		6		ok
TA3		7		ko

I want to creat a VAR table to store all my KO tasks :

DI		tache	statut
---		-----	------
TA1		4		ko
TA3		7		ko

using something like :

VAR _taches_ko_ = Selectcolumns (
        filter('table1'; table1[statut] = 'ko')
        ;"DI"        ; table1[DI]
        ;"tache"     ; table1[tache]
        ;"statut"    ; table1[statut]
    )

To finally show all the DI, which contain a KO tasks, with all their tasks :

DI		tache	statut
---		-----	------
TA1		1		ok
TA1		2		ok
TA1		3		ok
TA1		4		ko
TA3		7		ko

using (but not working properly) :

Var _DI_GMAOPS_ = Selectcolumns (
	FILTER(
		'Table1'
		; 'Table1'[tache] IN {SUMMARIZE(_tache_ko_; [tache])}
	)
        ;"DI"        ; table1[DI]
        ;"tache"     ; table1[tache]
        ;"statut"    ; table1[statut]
    )

Perhaps my approach is not good or optimized, feel free to correct it if needed

[v-diye-msft]

Hi @Anonymous 

It seems you'd like to get this one:

Table 2 = CALCULATETABLE(Table1,Table1[statut]="ko")

Table 3 = CALCULATETABLE(Table1,Table1[DI] in VALUES('Table 2'[DI]))

[Anonymous]

Hey,
Thank you for your answer, but my goal was not to create a new table and to use the temporary table in a VAR.

[v-diye-msft]

Hi @Anonymous 

You might consider providing your dummy pbix that would be folpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[Greg_Deckler]

@Anonymous - Not entirely clear. Perhaps use MAXX or MINX around your SUMMARIZE.