Forum Discussion

Plfrcs's avatar
Plfrcs
Frequent Visitor
2 years ago
Solved

Return a value from another table with row count parity conditions

Hi

 

I'm trying to return value from another table by checking uneven parity of counting row with calculations of conditions.

 

Currently i have to use multiple colum to check for each name wich ID is corresponding.

 

 

Is there a way to optimize this calculation which is too heavy to be publish with a large dataset ?

 

Here is the actual formula for each column :

VAR ID =  Table1[ID]

RETURN
IF(
    ISEVEN(
        SUMX(
            ADDCOLUMNS(
                    GENERATE(FILTER(Table1, Table1[ID] = ID ),
                     Table2 ),"@", INT ( (Table2[Name]="X")  && (Table1[Col1]-Table2[A]=Table1[Col2]))),
            [@] 
            )
        ),
    "No","Yes")

 

And the formula for deduce the name

 

Name = IF(Table1[Check X]="Yes","X",IF(Table1[Check Y]="Yes","Y",IF(Table1[Check Z]="Yes","Z","/")))

 

Thx

Need Help
  • Plfrcs's avatar
    Plfrcs
    2 years ago

    Thank's for Ahmedx for you solution. In the meantime I've found a solution that seems to perform better when refreshing. 

     

     

    Name = 
VAR __ID =  'Desired Tablet'[ID]
VAR __datas = ADDCOLUMNS(GENERATE ( FILTER ( Table1, 'Table1'[ID] = __ID ), FILTER(Table2, Table1[Col1]-Table2[A]=Table1[Соl2])),"@",1)
VAR __datassummerize = GROUPBY(__datas,Table1[ID],[Name],"@",SUMX(CURRENTGROUP(),[@]))
RETURN
MAXX(
   FILTER(__datassummerize, 
      ISODD([@])
   ),
   [Name]
)

     

     

2 Replies

  • Plfrcs's avatar
    Plfrcs
    Frequent Visitor
    2 years ago

    Thank's for Ahmedx for you solution. In the meantime I've found a solution that seems to perform better when refreshing. 

     

     

    Name = 
VAR __ID =  'Desired Tablet'[ID]
VAR __datas = ADDCOLUMNS(GENERATE ( FILTER ( Table1, 'Table1'[ID] = __ID ), FILTER(Table2, Table1[Col1]-Table2[A]=Table1[Соl2])),"@",1)
VAR __datassummerize = GROUPBY(__datas,Table1[ID],[Name],"@",SUMX(CURRENTGROUP(),[@]))
RETURN
MAXX(
   FILTER(__datassummerize, 
      ISODD([@])
   ),
   [Name]
)