Forum Discussion

CG20's avatar
CG20
Regular Visitor
2 years ago

Hi All, I need help for creating the below logic in PowerBI Desktop

I have two table Table1 & Table2, relationship between this tables is Many(Table1) to One(Table2) below is the structure & data of the tables Table1: ID Name Country 1 Aaa IND 1 Aaa...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,CG20.I am glad to help you.
According to your description, if you want to realize your needs: add two columns in Table1, you can refer to my solution.
Here are my test results

I created a measure M_ to determine whether the number of different Country values is greater than or equal to 2 after the current grouping based on Name, in which I used the SUMMARIZE function to create a virtual table to return the results of the filtering.

M_ =
VAR countryOver2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Country] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Name] = MAX ( 'Table1'[Name] ) )
    )
VAR nameCount =
    CALCULATE (
        COUNT ( 'Table'[Country] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Name] = MAX ( 'Table1'[Name] ) )
    )
VAR _table =
    SUMMARIZE (
        'Table1',
        Table1[ID],
        Table1[Name],
        Table1[Country],
        "count",
            IF ( countryOver2 >= 2 && nameCount >= 2, 1, 0 )
    )
RETURN
    MAXX ( FILTER ( _table, 'Table1'[ID] = SELECTEDVALUE ( Table1[ID] ) ), [count] )

2. I created two calculated columns Column_1, Column_2 to show the final filtering results

Column_1 = 
VAR result =
    IF (
        'Table1'[M_] = 1,
        CALCULATE (
            VALUES ( Table2[col1] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = "All"
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        ),
        CALCULATE (
            VALUES ( 'Table2'[col1] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = 'Table1'[Country]
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        )
    )
RETURN 

    result

Column_2 = 
VAR result =
    IF (
        'Table1'[M_] = 1,
        CALCULATE (
            VALUES ( Table2[col2] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = "All"
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        ),
        CALCULATE (
            VALUES ( 'Table2'[col2] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = 'Table1'[Country]
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        )
    )
RETURN 

    result

like this:

The final results are displayed below:

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

CG20's avatar
CG20
Regular Visitor
2 years ago

Hello v-jtian-msft,

 

Thank you for your solution, I have tried it on my data but it is giving me error of Column2 as below: 

 

And after implementing your solution for column1, I have tried but for majority of records it is showing me blank values, below is the ss for reference

Really thankful for your solution & would appreciate your reply on this message.

Regrads
CG20

 

 

 

  • CG20's avatar
    CG20
    Regular Visitor
    2 years ago

    Below is the example of data I have 

    If we look for ID = 13 in Table 2 we see the values of COL1 & COL2 it is TRUE & TRUE  for all the countries including the ALL value.
    But from your logic in Table1, Check1 column we are getting blanks / no values in Table1 for all the rows we should get TRUE value as in Country value from Table2 for ALL is TRUE.

     

    Note: And we had data for multiple versions in Table1 like you can see & the overall data of Table1 is in millions. 

    If there are any queries do reply.

     

    Thanks & Regards,

    CG20