Forum Discussion

pabburi's avatar
pabburi
Frequent Visitor
7 years ago

Create New table in power BI desktop

Hi All,

 

I have flat input data which has APPID(MANY) to join with other table having APPID (ONE) so the relation wil be ONE TO MANY kind.

 

Here i need to create a new table in power BI desktop for the flat input data in a format that could make ONE TO ONE realtionship with other table before building any visualization.

 

This new format i need to develop in query editor or Data but not using any visual in report tab.

 

Attaching the input and required output screenshots 

 

Need Help

1 Reply

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi pabburi,  

     

    From your description, it looks like you already created a 1:N relationship, may I know is there any specific purpose to build a 1:1 relationship instead?  

      

    Based on my research,  it’s not able to implement a table OUTPUT showing in your picture via query editor or Data view, to work around the issue, you can follow below steps:  

     

    Firstly, create new Intermediate table named A with unique APP-ID value, using  DAX below:

    A = DISTINCT(INPUT[APP-ID])  

     

    Then, create new relationship between table INPUT and table A ,setting Cardinality as "Many to one" and Cross filter direction as "Both". This means for filtering purposes, both tables are treated as if they're a single table. Finally ,you can make One to one relationship using table A with other table before building any visualization.    

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Attachment is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVU1Z7KcmzxBq9gyU7laT48ByqU-IWwqVX99FRQDSAgFEQ?e=EmsSna

     

    If you have any other issue, please feel free to ask.  

     

    Best Regards,

    Amy