Forum Discussion

Plfrcs's avatar
Plfrcs
Frequent Visitor
2 years ago
Solved

Countrows in another table with calculation filters between two tables

Hi   I would like to count rows in a another tables with calcultation bewteen thoses two tables for each row of the first table :     Does power bi support this calculation?   Thanks
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    Plfrcs add a new column in Table1 using the following DAX expression, the minimum requirement is that in Table1 you have a way to identify a unique row, and in this example, I used the date column to identify a unique row. If in your real data that is not possible then add an index column in Power Query and replace that with a Date column in the following dax expression:

     

    Count Rows = 
VAR __Date =  Table1[Date] 
VAR __Table1 = GENERATE ( FILTER ( Table1, [Date] = __Date ), Table2 )
VAR __AddFlag = 
ADDCOLUMNS ( 
    __Table1, 
    "@Flag", INT ( ( [X] + [A] + [B] = [Y] ) && ( MAX ( [Y], [X] ) <= [C] ) ) 
) 
RETURN
SUMX ( __AddFlag, [@Flag] )

     

     

     

     

parry2k's avatar
parry2k
Super User
2 years ago

Plfrcs add a new column in Table1 using the following DAX expression, the minimum requirement is that in Table1 you have a way to identify a unique row, and in this example, I used the date column to identify a unique row. If in your real data that is not possible then add an index column in Power Query and replace that with a Date column in the following dax expression:

 

Count Rows = 
VAR __Date =  Table1[Date] 
VAR __Table1 = GENERATE ( FILTER ( Table1, [Date] = __Date ), Table2 )
VAR __AddFlag = 
ADDCOLUMNS ( 
    __Table1, 
    "@Flag", INT ( ( [X] + [A] + [B] = [Y] ) && ( MAX ( [Y], [X] ) <= [C] ) ) 
) 
RETURN
SUMX ( __AddFlag, [@Flag] )