Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CG20
Regular Visitor

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:

IDNameCountry
1AaaIND
1AaaUSA
2BbbFRA
3CccBEL
3CccBEN
3CccARE
3CccAUS

 

Table2:

IDNameCountrycol1col2
1AaaINDTRUETRUE
1AaaAllFALSETRUE
2BaaFRAFALSETRUE
2BaaAllFALSETRUE
3CccBELTRUETRUE
3CccAUSFALSETRUE
3CccAllTRUETRUE

 

Expected output: (Need new calculated columns in Table1 output as follows)

IDNameCountryTF
1AaaINDFALSETRUE
1AaaUSAFALSETRUE
2BbbFRAFALSETRUE
3CccBELTRUETRUE
3CccBENTRUETRUE
3CccARETRUETRUE
3CccAUSTRUETRUE

 

Scenario is when distinct id eg(1) has two rows with different countries in table1, it will look for the same id(1) in table2
and in table2's country column we have "ALL" value then the values from column T & F should populate for both the rows in Table1 in new columns.

Thank you for reading & helping in advance.

4 REPLIES 4
v-jtian-msft
Community Support
Community Support

Hi,@CG20 
I'll share my test data with you so you could refer to it.

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.



v-jtian-msft
Community Support
Community Support

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

vjtianmsft_0-1715920355687.png

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:

vjtianmsft_1-1715920491922.png

The final results are displayed below:

vjtianmsft_2-1715920520678.png

 

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.

 

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: 
Reply.png

 

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

CG20_0-1716031992339.png

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

Regrads
CG20

 

 

 

CG20
Regular Visitor

Below is the example of data I have 

CG20_1-1716032803406.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.